An accurate source of historical data is mandatory for the market analysis as well as backtesting. Most of the traders use Excel sheets for stock data analysis, and until Aug 2018 Google finance was the best source of free historical data. Unfortunately, Google has deprecated their API, and now traders have to manually copy data from different sources into their Excel sheets. In order to avoid this manual work, we have come up with a solution to download historical stock data into Excel using AlphaVantage API.
AlphaVantage is another free source of historical as well as real-time market data and looks like a promising replacement of Google or Yahoo finance. You can use AlphaVantage API to download stock, currency and forex data into your Excel sheet. It provides some standard API endpoints to fetch data from different timeframes. Check the API documentation here.
You would need to download and install Microsoft Excel 2016 to use this utility. For 2010 or 2013 edition of Excel, you will require the latest PowerQuery Add-in.
How to use this Excel Sheet?
Follow the below steps to automatically download historical stock data into Excel sheet:
Step 1: Download the Excel sheet “Historical Stock Quotes.xlsm” from the end of this post.
Step 2: Open the Excel sheet, accept any warnings or ‘Enable content’ prompt.
Step 3: In the parameters screen, enter the symbol name and time interval for which you want to download data.
For US stocks, you can just enter the symbol name. While for other exchanges you would need to append the exchange code after the symbol. For example, to download data for stock “INFY” listed in NSE, you have to enter “INFY.NS”
Data is available in 1min, 5min, 15min, 30min, 60min, and daily timeframes. Use the time interval drop down to select your desired timeframe.
Step 4: Specify timezone offset from EST (eastern standard time). AlphaVantage natively provides data only in EST timezone, so this step is necessary to convert the timestamp in your local timezone. For example, use offset 9 hours 30 min to convert data from EST to IST timezone
Step 5: Click on the ‘Refresh Data’ button. Once you do that, the excel sheet would connect to AlphaVantage API and fetch data in real-time.
When you do this for the first time, you may receive few popups as below:
In the first popup, select https://www.alphavantage.co in the level drop down and click on connect.
In the second popup, select ‘public’ in both the drop downs and click on save.
Step 6: Once you have specified all the parameters correctly, the Excel will download the data in columns A to F.
Use the below link to download this Excel sheet. Let us know in the comments section if you face any issues: