Get Company Financial Data in Excel

This Excel spreadsheet downloads bulk financial information for multiple companies into an Excel spreadsheet. The spreadsheet is web-connected – it uses the Yahoo Finance API.

You can get financial data for 100 or more companies simply by entering their ticker symbols and clicking a button.

The VBA can be viewed and edited – nothing is locked or hidden away. Feel free to post your suggestions for improvements, or perhaps modify the spreadsheet yourself.

The spreadsheet is free – the download link is at the bottom of this post.

Start by entering in a list of stock tickers. You can enter tickers from US exchanges, as well as many European and international exchanges; you just need to know the right ticker symbol to use.

Then use the series of six drop down menus to choose the financial data you want.

After you click the “Get Data from Yahoo Finance” button, Excel employs some clever VBA to download your data.

You have access to 89 items of financial information for each stock from the Yahoo Finance API, including the ask price, EPS estimates for the current and next year, 52-week high and low, EBIDTA and many more. Simply use the drop-down menus to pick the data you want.

awesome spreadsheet, Thanks!
I am looking to also download data for EBITDA/EV, price-to-cahsflow and shareholder yield. Is there an easy way to include this in the spreadsheet (for someone illiterate in macros like me)?

Your tools are great. Do you have a general Excel function that can be used to request any specific stock data from any specific stock symbol? This would let me set up an analysis spreadsheet exactly the way I want to work.

I am getting a runtime error and when I debug it goes to the section of code starting with QueryQuote:
With DataSheet.QueryTables.Add(Connection:=”URL;” & qurl & “&f=” & DataSheet.Cells(3, tagNum), Destination:=DataSheet.Cells(4, tagNum))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False <—- THIS LINE OF CODE IS HIGHLIGHTED
.SaveData = True
End With

Hi Samir, awesome job on this spreadsheet. I am having the same problem as Shelby above where I get the runtime error ‘1004’ and when debugged it highlights:

.Refresh BackgroundQuery: = False

I have a listing of all tickers (about 8500) within Yahoo Finance (only using stocks listed on American Exchanges however) and when I input those tickers it gave me that error. The error occurs at the 180th line in excel meaning I can input 175 tickers before the error occurs.

I feel like its probably just a simple tweak in the code that I am missing. Any help you or anyone else could give me would be greatly appreciated 🙂 Thanks in advance.

Hi Samir, This is great. Thank you so much. I will be making a donation to say thanks for your efforts.

Could I trouble you to ask a question. I have created a sheet to put in my holdings (i.e. number of shares and total cost). What would you say is the best way to monitor my portfolio would you link a separate sheet to yours, or add sheets within the download? At this stage I only really want the price so I can see my gain/loss. I’m pretty new to this so forgive me if its obvious?

Yahoo throttles the amount of data you can download in one call. I need to rewrite the VBA so that it only downloads e.g. 50 tickers at a time (or a similar amount). Bear with me – my 18 month old son keeps me busy in the evenings and weekends so I don’t have much free time.

It’s a problem with Yahoo (although it’s not really a problem). They restrict programmatic access to financial data to a set of companies. Those Canadian companies you’ve tried may not be on the white list.

Why this file is not giving output for Indian stocks / Equity ?
for example WIPRO.NS is yahoo ticker for WIPRO Limited but this is not working , i mean non of indian stock is not working on this excel.

I’d like to extend the workbook by creating historical sheets. For each selected value (e.g. “day’s low”) I can copy the non-blank values to a sheet with a similar name. I could add a column to the tags sheet with this name.

I’m having problems however, accessing the selected value. With “Day’s Low” in D4, range(“D4”).value is blank. I suspect this behavior may be tied to the password protection of the sheet. I’ll make a donation, then await your replay.

Hi,
I really liked this worksheet, I want to put together an analysis with all the actions listed in Brazil in a single worksheet for analysis, how do I add a list of 100 companies and a few more columns?