Download Historical Stock Data into Excel using AlphaVantage API

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.

Prerequisites

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.

Excel Parameters

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

Download Historical Stock Data into Excel

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.

Prompt1

In the second popup, select ‘public’ in both the drop downs and click on save.

Prompt2

Step 6: Once you have specified all the parameters correctly, the Excel will download the data in columns A to F.

Download Historical Stock Data into Excel using AlphaVantage API

Download Link

Use the below link to download this Excel sheet. Let us know in the comments section if you face any issues:

Historical Stock Quotes

11 Comments

    • Hi Nikhil,

      AlphaVantage fetches data for past 100 bars. We’ll check if there is any option to increase the limit. Thanks for your feedback

    • Hi Vaibhav,

      What is the version of Excel you are using? The Excel sheet is compatible only with 2013+ versions

    • Hi Vaibhav,

      We would be updating OBV and other realtime excel sheets once the beta testing for this is completed

        • Hi Nitin,

          I fear there is no option currently to fetch index data. Will check with AlphaVantage support team and let you know.

  1. Hi. What is the Symbol code for Nifty Index to be used ? Tried many options “Nifty” , “Nifty 50”, “Nifty 50.NS” but none of them is working

Leave a Reply

Your email address will not be published. Required fields are marked *