Home
»
Excel
»
Trading
» Excel Macro : Extract historical data for multiple stocks
Excel Macro : Extract historical data for multiple stocks
This tutorial covers excel macro which you can use to download historical data for multiple stock quotes. It is useful when you need to perform detailed technical analysis on stocks you wish to invest. Here source of the data is Yahoo Finance.
In this excel macro, you have flexibility to play around with the following arguments -
Period You can select the frequency of historical data for stocks - Daily, Weekly, Monthly. By "daily" it refers to every day's low, high and closing price along with volume. Weekly refers to prices at the start of each week (one entry for one week). Similary monthly corresponds to one record for a month
Starting Date Date on which you want historical data starts from
End Date Date on which you want historical data ends with.
Allows specifying multiple stocks. Data to be appended once data for first stock completes.
Output of stocks downloader look like below.
Reason for data for 15th February is not included because stocks specified in the input are US stocks and Nasdaq stock market was closed on that day on eve of Presidents' Day in US. Data for 22nd February was excluded because End Date argument is not inclusive of the date. If you want data for 22nd February, specify 23rd February in the end date argument.
Fetch Historical Data for Non US Stocks
For US Stocks Yahoo Finance does not require any suffix added in symbols or tickers. You can use it directly. However suffix needs to be entered for non US stocks. See some of the examples below.
Canadian Stocks
Indian Stocks
DOL.TO
RELIANCE.NS
ENGH.TO
TCS.NS
AC.TO
HCLTECH.NS
Table below shows a complete list of stock exchanges across world and corresponding suffix used for fetching stock quotes from Yahoo Finance.
Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he has worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and Human Resource.
While I love having friends who agree, I only learn from those who don't Let's Get Connected Email LinkedIn
3 Responses to "Excel Macro : Extract historical data for multiple stocks"
An error occurs when start date and end date are the same. Subscript out of range on this statement:
ReplyDeleteReDim resultArray(0 To UBound(csv_rows), 0 To nColumns) As Variant
Returned data does not include end date.
ReplyDeleteAn error occurs when running when executing the subroutine "getCookieCrumb" Excel debugger highlights the .send in the routine.
ReplyDeleteAny suggestions how to fix this?