Excel Macro : Extract historical data for multiple stocks

Deepanshu Bhalla 5 Comments ,
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. End Date is not inclusive of the date.
  • Allows specifying multiple stocks. Data to be appended once data for first stock completes.
Stocks Downloader
Stocks Downloader Output
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.
Fetch latest Data
In order to fetch latest data for a stock, you can put latest date againt Starting Date and (latest date + 1) as End Date. For example If you want data for 22nd February, specify 23rd February in the End Date argument and 22nd February in the Starting 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
Incase you get this error : Microsoft has blocked macros from running because the source of this file is untrusted
Solution
  1. Go to the folder where this macro file is located.
  2. Right-click the file and choose Properties from the menu.
  3. At the bottom of the General tab, select the Unblock checkbox under security option and then click OK.

Table below shows a complete list of stock exchanges across world and corresponding suffix used for fetching stock quotes from Yahoo Finance.

Country Market Suffix
Argentina Buenos Aires Stock Exchange (BYMA) .BA
Austria Vienna Stock Exchange .VI
Australia Australian Stock Exchange (ASX) .AX
Belgium Euronext Brussels .BR
Brazil Sao Paolo Stock Exchange (BOVESPA) .SA
Canada Canadian Securities Exchange .CN
Canada NEO Exchange .NE
Canada Toronto Stock Exchange (TSX) .TO
Canada TSX Venture Exchange (TSXV) .V
Chile Santiago Stock Exchange .SN
China Shanghai Stock Exchange .SS
China Shenzhen Stock Exchange .SZ
Czech Republic Prague Stock Exchange Index .PR
Denmark Nasdaq OMX Copenhagen .CO
Egypt Egyptian Exchange Index (EGID) .CA
Estonia Nasdaq OMX Tallinn .TL
Europe Euronext .NX
Finland Nasdaq OMX Helsinki .HE
France Euronext Paris .PA
Germany Berlin Stock Exchange .BE
Germany Bremen Stock Exchange .BM
Germany Dusseldorf Stock Exchange .DU
Germany Frankfurt Stock Exchange .F
Germany Hamburg Stock Exchange .HM
Germany Hanover Stock Exchange .HA
Germany Munich Stock Exchange .MU
Germany Stuttgart Stock Exchange .SG
Germany Deutsche Boerse XETRA .DE
Greece Athens Stock Exchange (ATHEX) .AT
Hong Kong Hong Kong Stock Exchange (HKEX)*** .HK
Hungary Budapest Stock Exchange .BD
Iceland Nasdaq OMX Iceland .IC
India Bombay Stock Exchange .BO
India National Stock Exchange of India .NS
Indonesia Indonesia Stock Exchange (IDX) .JK
Ireland Euronext Dublin .IR
Israel Tel Aviv Stock Exchange .TA
Italy EuroTLX .TI
Italy Italian Stock Exchange .MI
Japan Tokyo Stock Exchange .T
Latvia Nasdaq OMX Riga .RG
Lithuania Nasdaq OMX Vilnius .VS
Malaysia Malaysian Stock Exchange .KL
Mexico Mexico Stock Exchange (BMV) .MX
Netherlands Euronext Amsterdam .AS
New Zealand New Zealand Stock Exchange (NZX) .NZ
Norway Oslo Stock Exchange .OL
Portugal Euronext Lisbon .LS
Qatar Qatar Stock Exchange .QA
Russia Moscow Exchange (MOEX) .ME
Singapore Singapore Stock Exchange (SGX) .SI
South Africa Johannesburg Stock Exchange .JO
South Korea Korea Stock Exchange .KS
South Korea KOSDAQ .KQ
Spain Madrid SE C.A.T.S. .MC
Saudi Arabia Saudi Stock Exchange (Tadawul) .SAU
Sweden Nasdaq OMX Stockholm .ST
Switzerland Swiss Exchange (SIX) .SW
Taiwan Taiwan OTC Exchange .TWO
Taiwan Taiwan Stock Exchange (TWSE) .TW
Thailand Stock Exchange of Thailand (SET) .BK
Turkey Borsa İstanbul .IS
United Kingdom London Stock Exchange .L
Venezuela Caracas Stock Exchange .CR
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

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 worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

5 Responses to "Excel Macro : Extract historical data for multiple stocks"
  1. An error occurs when start date and end date are the same. Subscript out of range on this statement:

    ReDim resultArray(0 To UBound(csv_rows), 0 To nColumns) As Variant

    ReplyDelete
    Replies
    1. Error handling for the same has been added. Thanks!

      Delete
  2. Returned data does not include end date.

    ReplyDelete
  3. An error occurs when running when executing the subroutine "getCookieCrumb" Excel debugger highlights the .send in the routine.

    Any suggestions how to fix this?

    ReplyDelete
Next → ← Prev