Excel Financial Data Add-In
Ever since Microsoft (MSFT) added the ability to import external data using a web query to Excel, I have been interested in importing financial data into my spreadsheets. I am always looking for ways to leverage my technical skills to improve my investment research. When I recently came across a discussion on some Yahoo! Groups regarding an Excel add-in plugin being developed to add free financial data from websites directly into Excel formulas, I was very interested.
I discovered the free Stock Market Functions Add-In developed by Randy Harmelink and I downloaded it right away. I was amazed at how powerful and useful this plugin was right away. This add-in can retrieve over 4,000 data elements from various financial websites. These sites include MSN, Yahoo, BarCharts, Business Week, Zacks, Reuters, Google, and a few others. The excellent documentation with the Stock Market Functions add-in lists all the numerical codes needed to access an individual stock financial or technical data element. More details regarding this Excel add-in are available at the smf_addin page. Randy Harmelink is interested in gaining more feedback regarding the Excel Stock Market Functions Add-In so be sure to post any feature requests or bug reports to the smf_addin email list.
I plan on using the Stock Market Functions Add-In to build a spreadsheet that automatically calculates a rough intrinsic value of stocks that I’m researching. I hope to use the add-in to retrieve information from cash flow statements to build a simple discounted cash flows (DCF) model and then estimate whether or not there is a margin of safety associated with the current stock price. I’ll share my spreadsheet here when it gets further developed.
If you are interested in using Excel as a stock market tool, you might also be interested in all the work being done at the XLTraders Yahoo Group. The XLTraders list was the original home of the Stock Market Functions Add-In before it recently spun-off into the newly formed smf-addin group. The description of the XLTraders group states, “The purpose of this list is to exchange ideas and techniques for using Excel in trading. This includes VB/VBA code as well.” While much of the discussion on this list focuses on tools for active traders, much of the information is also useful for value investors.
If you use Excel for your investing research, I’m curious as to what other web resources, tools, or spreadsheet templates you use. Please share them below.
You might be interested in the Rule One Forum (www.ruleoneforum.com) spreadsheet as well, if you’ve not already seen it. It’s based on the principles in Phil Town’s “Rule #1” book, pulls data from MSN MoneyCentral, and then tries to calculate an intrinsic value and a margin-of-safety price.
Hmm, I was just searching for something like this a few weeks ago, and couldn’t find anything, so thank you for posting about it. From just a rudimentary speadsheet i just put together, it will cut my “screening” time for good candidates in half.
…I hope to use the add-in to retrieve information from cash flow statements to build a simple discounted cash flows (DCF) model…
Hopefully, the ability should be available soon. I’ve got the AdvFN financial statement elements all mapped out, but have to test the mappings. I’m getting worried about limits though. If I add all of the information for 10 years of the AdvFN financial statements, that’s an additional 2810 data elements. And I was going to add 20 quarters of their quarterly statements, which would be another 5620 data elements.
Andy, thanks for pointing out the Rule One Forum spreadsheet.
Max, I’m glad I could help you.
Randy, I think the 20 quarters of quarterly data is excessive. I feel that at most one would only need data on the past eight quarters. The annual data is sufficient for more historical information needs.
I also noticed that there is both Reuters and Google data accessible by the add-in. Did you know that most of Google’s data comes from Reuters? If you want to cut down on the number of data elements, I recommend removing the redundant Google data, especially since Google Finance could still change a lot given that it is still in beta. There might be similar examples of the same data coming from the same parent source being reported on more than one site. I would focus on picking maybe only the original source or the most reliable provider and dropping the redundant data elements.
I really look forward to being able to access the ADVFN data. Thank you and keep up the great work Randy.
All of the Google Finance items can be retrieved in two web requests, while the Reuter’s requires six. Also, I think the Google data has more detail (# of decimals), or at least did at one point. Plus, I think all of the Google items are mapped, but only a subset of the Reuters. Because of discrepancies in financial statement reporting, I was getting the data from a number of sources for comparison. I think there was also an issue on at least one company where the Google data was more current.
One reason for so much quarterly financial data is so I can do some rolling years of MFI data analysis. Because AdvFN returns 5 quarters at a time, 20 quarters was the lowest common denominator of 5 and 4.
The annual testing went well — only three data items had problems. But I do have a concern whether AdvFN will be consistent in format. I originally set up my parsing for particular strings, but ran into different strings later — two spaces in the middle of a description instead of the one I had seen previously. Looking at the quarterly, it appears to be back to a single space. Not sure how to handle that, especially since it could happen on almost any string I’m searching for.
Quarterly comparison just finished recalculating — everything matched. I’ll be uploading the new release later today.
I’ve noticed that financial statements of ADR’s at Google are presented in foreign currencies. This is in contrast to the same numbers at Yahoo, which are in US$.
How do you plan to deal with these types of conversions?
tweakie — No plans at this point. The add-in just collects what is provided. It’s one of the reasons I have the same data items collected from several sources — the same data item is not always the same data item.
The Update on the Excel Stock Market Functions is fantastic you can see it too on at the smf_addin Yahoo Group
you can also use Financial Link for Excel to load updating stock quotes, financial statements, estimates, price histories into Excel. Easy-to-use formulas to load the data and also wizards to help you creating the formulas.
More detailed information here:
http://www.xlconsulting.net/flink.php
You may want to try the free Excel Add-in from Edgeseeker. It is similar to Randy’s but allows data querying across multiple data sources. All data are stored locally in a SQLite database so subsequent searches do not require downloads other than to update when new data are available. Also, data may be pre-loaded to facilitate working offline.
Enjoy!
Edgeseeker
After reading your post i too interested in using Excel as a stock market tool. I really impressed in the way you explained. Good keep it up.
There’s an Excel spreadsheet at http://optimizeyourportfolio.blogspot.com/2011/05/importing-historical-stock-prices-from.html which automatically downloads financial data from Yahoo with VBA. You don’t need to install an add-in
http://investexcel.net/222/automatically-download-historical-forex-data-into-excel/ has a cool spreadsheet that imports historical forex data into Excel using some VBA. It’s pretty simple to use.
There’s another spreadsheet at http://investexcel.net/218/importing-historical-stock-prices-from-yahoo-into-excel/ which imports historical stock quotes from Yahoo into Excel
You have not seen anything yet… I am building the a similar thing to Randy’s but include RTD and DDE quotes with up to 15 years of annual data and 30 quarters. Not as flexible of Randy’s but heak, i am building it for me…
Take a look at the Tools at http://www.investingwithexcel.com They have tools that import Financial statements up to years without all the headache of the SMF addin. Also if you email the author he will grant you access to the VBA Code.
Correction to my last post, investinwithexcel.com brings in over 10 years of financials
Sody thanks for the vote of confidence in my site. I just added another tool a Stock History tool using non other then the code from Randy H. and the SMF add-in mentioned on this site.
Take a look: investingwithexcel.com
An Excel function that returns any field in the Yahoo Finance XML stream can be found here
http://www.philadelphia-reflections.com/blog/2392.htm
For current Treasury yield-curve rates:
http://www.philadelphia-reflections.com/blog/2388.htm
You can also check out http://spearian.com for market data
YCharts provides a comprehensive Excel Plug-in for financial data.
http://ycharts.com/excel
Automate Your Financial Models
Inject our financial data into your comparable analyses, portfolio performance tracking, custom benchmarking, indexing projects or any of your other core research models.
Update your entire spreadsheet with the most recent data with one click.
Use cell references for metric codes, dates, and companies for easy modifications to in-depth models.
Leverage Our Expertise
Work with one of our dedicated experts to convert your existing models with our syntax.
Get started with one of our pre-built templates; several are available to download and begin using immediately.
YCharts’ Excel Add-In is available with a subscription.