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.
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.
Download Link
Use the below link to download this Excel sheet. Let us know in the comments section if you face any issues:
no able to fetch 1 min data for the entire day. can you plz help
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 Admin,
Can you kindly add the option for Hiekin Ashi instead of normal candlestick.
iT IS GETTING ERROR AS FOLLOWS
Run-Time Error 1004 CLASS NOT REGISTERED
Hi Vaibhav,
What is the version of Excel you are using? The Excel sheet is compatible only with 2013+ versions
and do not have OBV
Hi Vaibhav,
We would be updating OBV and other realtime excel sheets once the beta testing for this is completed
HI. What symbol code to be used in Excel for Nifty Index Live Data
Hi Nitin,
I fear there is no option currently to fetch index data. Will check with AlphaVantage support team and let you know.
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
HI. What symbol to be used for Nifty Index Live data
thanks for your efforts
Hi is there anyway of getting 5, 10, 15 min timeframe data for last 1 year or even 6 months. I have tried all but none is working. I am ready to pay
Hi Rohit,
Check in NSE website, you should be able to get it for a yearly payment
hi sir … i need NIFTYFUTURE sheet to track OI & PRICE changing for every 15min.
I want all F & O stocks live data in single excel sheet
Dear Sir,
Thanks a lot for such great job.
Is this possible to make this excel file compatible with Microsoft excel 2010 version.So that we will be saved from this power query etc. issue. Since when I downloded this power query 32 bit for 2010 version ..its asking for Microsoft excel 2010 SP1 ..thus we are not able to use this great file of you for such technical reasons. Please make it available to everybody use and oblige.
Hi Vishnu,
Unfortunately the Excel can’t be made compatible with Excel 2010
Hi sir, how can I check the data for NASDAQ? Say MSFT?
MSFT.NA or MSFT.NASDAQ is not working for me in the “symbol” field.
Hi sir, how can I check the data for NASDAQ? Say MSFT?
MSFT.NA or MSFT.NASDAQ is not working for me in the “symbol” field.
Fantastic work… but I’m getting error [Expression Error] for all time frames I’m choosing
Hi
Can you help me in getting mcx data. Thanks
Is it working as of now, doesn’t get any data and not showing any error also…
Hi Tarun,
Yes it is working in Excel 2016
hi … can we add another 4 sheets to existing one . 5 min, 15 min , 30 min, hourly and daily .. with auto refresh option .. tried to do asking for VBA password
Hi Ravindar,
You can specify the timeframe in the input
hi,
i cant get all the symbols correctly. so can u pls tell me whr to get the symbols of stocks
Thanks a lot, its relay us full
how can i get NIFTYFUT data like this sheet. please help me
Hi Sam,
Alphavantage does not support index data as of now
What trying to get data , I am getting the following error ” The filed ‘Time Series (15 mins)’ of the record wan’t found. .Can you please help. I am unable to download the data
Hi George,
Which symbol you are trying to fetch data for? Looks likes the default “INFY.NS” symbol is no longer supported by Alphavantage API. I tried with SYMBOL “IBM” and it works fine.
Sir when i select INFY.NS then it shows error
[Expression.Error] The field ‘ Time Series (60 min)’ of record was’nt found.
It shows the same in every time interval
Hi Shubham,
Looks likes the default “INFY.NS” symbol is no longer supported by Alphavantage API. I tried with SYMBOL “IBM” and it works fine.
It seems Indian stock doesnt work with this excel sheet.. Kindly advice if there is any other way?
Using Excel for Mac on a 365 subscription. I get an error/Alert: [Expression.Error] The module named ‘Excelinterop’ has been disabled in this context.
I am not able to fetch data from alpha api as it states that [Expression.Error] The field “Time series daily” of the record was not found
Hi Vedprakash,
Alphavantage API is currently not supporting most of the Indian stocks
I would like to review the provided code, is the VBA password available?
Hello.It seems data for Indian stocks isn’t extractable through Alphavantage API.Kindly suggest some other API/method to get it.Thanks.
That’s correct Nishant, Alphavantage no longer supports Indian stocks. We are looking for an alternative right now.
Sir, Did you find an alternative?
Is there a way to get more records than 100? On the website it shows outputsize can be changed, but not sure how to modify Excel to achieve that.
By default, outputsize=compact. Strings compact and full are accepted with the following specifications: compact returns only the latest 100 data points in the intraday time series; full returns the full-length intraday time series. The “compact” option is recommended if you would like to reduce the data size of each API call.
Hello. Is there any way to get more result than 100 ?
can you able to add / provide historical cumulative open interest data