***Apologies, this program is not currently fully functional. It would appear that ADVFN have updated the website in such a manner that prevents seamless code progression. I will update the code once the semester is finished and I have some spare time. In the mean time, the program may still be of some use so feel free to download and try anyway.***
Have you guys ever struggled to find free historical financial data for publically listed companies short of tediously digging it out of countless annual financial statements? I had until I stumbled across ADVFN. I soon realised, however, the way ADVFN presents its financial data is far from analysis friendly. Some companies have financial earnings data dating back to 1993 but it is only presented in tables for a maximum of 5 years at a time, requiring further webpage navigation to access the remaining years. Naturally, this makes it quite difficult to analyse the data and value the stocks.
For a bit of fun, I decided to write a macro in VBA that scrapes all the financial earnings data for a list of publically traded companies from the ADVFN website and puts it into a spreadsheet for easy analysis. You can download the spreadsheet with built in macro from the below link. Once the download is complete all you need to do is press the big green button to start scraping earnings data from the website. Just don’t forget to enable macros if you haven’t already (typically this is not required as macros are already enabled within the spreadsheet), and be sure to save the spreadsheet locally as a macro enabled workbook.
Download Spreadsheet with Macro here…
To download financial data from the full three thousand odd companies listed on the New York Stock Exchange the macro is going to need to run for a while. It took me approximately 50 continuous hours to complete the download which is over 8 million data values – the resulting spreadsheet is over 41MB in size. If you do need to shut down your computer for the night though it’s no problem, simply save and close the spreadsheet and shut down as normal. After you reboot, open the spreadsheet and restart the macro. It will pick up right where it left off.
Unfortunately, not all publically traded companies around the globe have historical financial data available on the ADVFN website. It seems to me that only companies listed on the New York Stock Exchange have financial data available but hopefully this is soon to change.
I have pre-populated the spreadsheet with a list of Ticker symbols from companies listed on the New York Stock Exchange but you might like to try adding others or removing some that return an error (I have only left these in the spreadsheet as they seemed to form part of an official list), just remember to change the value for the string ‘Exchange’ in the code accordingly if you find ADVFN posted earnings data for another stock exchange.
I noticed that the typical loop code segment used for ensuring the internet site has completely downloaded prior to executing any additional code (Do Until IE.readystate = 4 etc.) sometimes gets caught in an infinite loop. To combat this I have nested the loop inside another loop which breaks the initial loop after a predefined amount of time and forces the page to reload. This predefined value of time is called ‘resetTime’ within the code and, depending on your internet speed, you may like to change the value. I have it set to 6 seconds which seems to be optimal given the functionality of my ‘Command Centre’ 😛 but this may differ for your system.
This version of the macro I wrote will only download financial data available from the year of download (2015 at the time of posting) back to the earliest posting on ADVFN, i.e. you will not be able to use it a year later to simply download an extra year of financial data for each company. You can restart the download from scratch but as mentioned previously, that will take a while. I will work on improving this with version 2 of the macro.
Please note that I have only been able to test the macro on an Australian server with the Australian version of the ADVFN website, and thus I cannot guarantee the macro will function as intended in other countries. If it proves to function inadequately in your country it may only require a very simple fix. Open the ADVFN website on your computer, type in any ticker symbol listed on the New York Stock Exchange to the “Get Quote” search box and hit enter, click on the “Financials” tab towards the top and then select “Annual Reports”. Make sure the URL at this page matches up with the URL coded into the macro. If not, change as required. Hopefully the fix is this simple.
Additionally, as I run a windows operating system on my ‘Command Centre’, I have not been able to test the macro using an Apple Operating System, or any other OS for that matter, and thus cannot guarantee the macro will function as intended on non-Windows OS’s.
I have intentionally not uploaded the spreadsheet populated with financial data I previously scraped from the ADVFN website due to it possibly being in breach of the websites T’s and C’s. The website seems to allow sharing of its data to third parties but does not permit that data to be changed in any way or form. The macro I wrote modifies the data from the website slightly upon download in the following ways:
- Multiplies applicable data values by one-million (as some of the values are shown in millions of US Dollars) to produce full numbers which can easily be analysed;
- Re-formats data vales according to their type, e.g. percentage, currency or date;
- Consolidates the two types of tables companies financial earnings data can be represented in, i.e. some companies have 203 rows of data per year and others have 307 and the row titles are not always consistent. Although the row headings I have adopted are not listed in the spreadsheet initially upon download, the “Tickers” sheet details the line numbers from both table sizes (203 rows and 307 rows) which have been used and consolidated. The row titles will soon download from the website after initiating the macro but the row headings for each table size will only download once the macro has encountered that particular table size, e.g. the row headings for the short table may not download until the 10th stock when a company with data represented in a short table is encountered.
Please be advised, if you choose to download this ADVFN scraper, you are obligated to adhere to ADVFN’s terms and conditions shown on their website. I have listed Section 4: “Copyright and Your Rights” of the T’s & C’s below for easy reference.
- COPYRIGHT AND YOUR RIGHTS
Copyright and all other intellectual property rights subsisting in the database accessible via the Services and each and every piece of information provided through the Services (the “Information”) is owned by ADVFN or the providers of such information (“Data Providers”). Reproduction of part or all of the contents in any form is prohibited other than in accordance with the following permissions:
Licence to copy for personal use:
You may print or download to a local hard disk extracts from the Information amounting to no more than four individual pages for your personal use only. Licence to recopy for limited purposes:
You may recopy to third parties for their personal information only certain Information as may be specified from time to time by ADVFN as being available for that purpose, but only if:
- you clearly and prominently acknowledge the ADVFN Web page as the source of the material; and
- you inform third party that these conditions apply to him, her or it and that he/she/it must comply with them. This licence to recopy does not permit alteration, adaption or merging of material available on these pages. No part of the ADVFN Web pages may be distributed or copied for any commercial purpose.
No part of the ADVFN Web pages may be reproduced on or transmitted to or stored by any other Web site or other form of electronic retrieval system by any person or entity that operates any such Web site or electronic retrieval system.
If after you download the spreadsheet it does not function as advertised here, I do apologise, I am fairly new to this. Please post a brief description of the problem you are experiencing in the comment section below and I will endeavour to assist as soon as possible.
Thanks and happy analysing.
Disclaimer
I am solely providing a means for efficiently accessing a portion of the data posted on the ADVFN website. The information contained in this website is for general information purposes only. While I endeavour to keep the information up to date and correct, I make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products or services on the website for any purpose. Any reliance you place on such information is therefore strictly at your own risk.
In no event will I be liable for any loss or damage including without limitation, indirect or consequential loss or damage, or any loss or damage whatsoever arising from loss of data or profits arising out of, or in connection with, the use of this website.
nicksfinancetricks 