How to Download Historical Stock Data to Excel for Free

Deepanshu Bhalla 44 Comments

This tutorial covers how to download historical stock prices directly into Excel for free. It automates the extraction of stock data for multiple stocks. It is useful when you need to perform detailed analysis of stocks which you wish to invest. Here source of the data is Yahoo Finance.

Update : The macro can run on both Windows and macOS.
Features
  • Global Stock Market Access: View stock data from markets around the world.
  • Download Stock Data Automatically : Get stock data for multiple stocks at once.
  • Access Historical Data : View past stock data to see how stocks have performed over time.
  • Get Live Updates : See current stock prices in real-time.
  • Choose Data Intervals : Get data in daily, weekly or monthly chunks.
  • Minute and Hourly Data: Access detailed stock data for every minute or hour.
How to Use Macro
Incase you get this error : Microsoft has blocked macros from running because the source of this file is untrusted
  1. Go to the folder where the downloaded 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.
  4. Open the file and enter the stock tickers for which you want to download historical information, starting from cell A10.
  5. Specify period, start and end date and then hit "Get Data from Yahoo Finance" button.

In this excel macro, you have flexibility to play around with the following arguments -

  • Interval You can select the frequency of historical data for stocks - Minute, Hourly, Daily, Weekly, Monthly.
  • Starting Date Date on which you want historical data starts from
  • End Date Date on which you want historical data ends with. Please note that the end date is not included in the range.
Download Stocks Data in MS Excel
Stocks Downloader Output

Output of stocks downloader look like the image shown above.

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 does not include the specified date.
How to Get Latest Stock Data in Excel

There are two ways to get the real-time price of a stock.

  1. Set start and end date blank. Make sure period is set as 'Daily'.
  2. Set the latest date as the Starting Date and (latest date + 1) as the 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.
How to Get Minute Time Frame Stock Data in Excel

In cell B6, specifying 5 means five-minute time frame when interval is "Minute". In cell B7, specifying 2 means that two days of data are required for a 'minute' or 'hourly' interval.

Please note that the start date and end date arguments are not considered when the interval is set to either "Minute" or "Hourly".
Download Minute-Level Stock Data into Excel

As shown in the image below, we have stock data with a five-minute time frame presented in tabular format in Excel.

Output of Minute-Level Stock Data in Excel

The macro returns time in Coordinated Universal Time (UTC) format. To convert it to local time format, you can use the formula below.

Example 1: Eastern Daylight Time (EDT) is 4 hours behind UTC so we will subtract 4 to the red highlighted section in the following formula.

=TEXT(MID(A2,1,LEN(A2)-4)-4/24,"mmm dd, yyyy hh:mm:ss")

Example 2: Indian Standard Time (IST) is 5 hours and 30 minutes ahead of UTC so we will add 5.5 to the red highlighted section in the following formula.

=TEXT(MID(A2,1,LEN(A2)-4)+5.5/24,"mmm dd, yyyy hh:mm:ss")
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.

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
VBA Code

The following VBA code assumes that the period (Daily/Weekly/Monthly) is specified in cell B3, with the start and end dates for which you want stock data in cells B4 and B5. In cells B6 and B7, it is assumed that the time frame and data duration are provided for either a 'minute' or 'hourly' interval data requirement. The symbols of the stocks should start from cell A10. Please refer to the macro file for detailed information on the input controls.


Sub GetData()
    Dim InputControls As Worksheet
    Dim OutputData As Worksheet
    Dim symbol As String
    Dim startDate As String
    Dim endDate As String
    Dim period As String
    Dim rng As Integer
    Dim last As Double
    Dim OffsetCounter As Double
    Dim Result As Integer
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual

    'Sheet Names
    Set InputControls = Sheets("Sheet1")
    Set OutputData = Sheets("HistoricalData")
    
    With InputControls
        last = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
        
    ' Arguments
    startDate = (InputControls.range("B4") - dateValue("January 1, 1970")) * 86400
    endDate = (InputControls.range("B5") - dateValue("January 1, 1970")) * 86400
    period = InputControls.range("B3")
    timeframe = InputControls.range("B6")
    rng = InputControls.range("B7")
    
    Dateminus = -InputControls.range("B4") + InputControls.range("B5")
    
     If period = "Minute" Or period = "Hourly" Then
        If Len(timeframe) = 0 Or Len(rng) = 0 Then
            MsgBox "Cells B6 and B7 cannot be empty when Interval is 'Minute' or 'Hourly'", vbCritical
            Exit Sub
        End If
      Else
          If Dateminus < 1 Then
        MsgBox ("Date difference must be atleast one. Since EndDate is not inclusive of the date, you can have one day difference between start and end Date to fetch latest price")
        Exit Sub
     End If
     End If
    
    
    ' Period
    If period = "Daily" Then
        period = "1d"
    ElseIf period = "Weekly" Then
        period = "1wk"
    ElseIf period = "Monthly" Then
        period = "1mo"
    ElseIf period = "Minute" Then
        period = timeframe & "m"
    ElseIf period = "Hourly" Then
        period = timeframe & "h"
    End If
    
    
    OutputData.range("A2:H1000000").ClearContents
    
    'Loop over multiple symbols
    For i = 10 To last
    symbol = InputControls.range("A" & i).value
    OffsetCounter = 1
    Call ExtractData(symbol, startDate, endDate, period, rng, OffsetCounter, OutputData)
    Next i

    OutputData.Columns("A:H").AutoFit
    
    Application.Calculation = xlCalculationAutomatic
    InputControls.Select
    MsgBox ("Task Accomplished. See HistoricalData Tab")
        
End Sub

Sub ExtractData(Symbols As String, startDate As String, endDate As String, period As String, rng As Integer, OffsetCounter As Double, OutputData As Worksheet)

    Dim resultFromYahoo As String
    Dim objRequest
    Dim nColumns As Integer
    Dim tickerURL As String
    Dim symbol As String
    Dim openPrices() As String
    Dim highPrices() As String
    Dim lowPrices() As String
    Dim closePrices() As String
    Dim adjClosePrice() As String
    Dim volumes() As String
    Dim timestamps() As String
    Dim i As Integer


    If (Right(period, 1) = "m" Or Right(period, 1) = "h") And rng > 0 Then
        tickerURL = "https://query2.finance.yahoo.com/v8/finance/chart/" & Symbols & _
        "?interval=" & period & "&range=" & rng & "d"
    Else
        tickerURL = "https://query2.finance.yahoo.com/v8/finance/chart/" & Symbols & _
            "?period1=" & startDate & _
            "&period2=" & endDate & _
            "&interval=" & period
    End If

    Set objRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    With objRequest
        .Open "GET", tickerURL, False
        .send
        .waitForResponse
        resultFromYahoo = .responseText
    End With
    
   
    ' Extract symbol
    symbol = ExtractValue(resultFromYahoo, """symbol"":""", """")
    
    ' Extract open prices (array)
    openPrices = Split(ExtractValue(resultFromYahoo, """open"":[", "]"), ",")
    
    ' Extract high prices (array)
    highPrices = Split(ExtractValue(resultFromYahoo, """high"":[", "]"), ",")
    
    ' Extract low prices (array)
    lowPrices = Split(ExtractValue(resultFromYahoo, """low"":[", "]"), ",")
    
    ' Extract close prices (array)
    closePrices = Split(ExtractValue(resultFromYahoo, """close"":[", "]"), ",")
       
    ' Extract volume (array)
    volumes = Split(ExtractValue(resultFromYahoo, """volume"":[", "]"), ",")
    
    ' Extract timestamps (array)
    timestamps = Split(ExtractValue(resultFromYahoo, """timestamp"":[", "]"), ",")
    
    ' Extract adj close price
    adjClosePrice = Split(ExtractValue(resultFromYahoo, """adjclose"":[{""adjclose"":[", "]}]"), ",")

        
    nColumns = 7
    numberOfEntries = UBound(openPrices) - LBound(openPrices) + 1
    ReDim combinedData(1 To numberOfEntries, 1 To nColumns)
    For i = 1 To numberOfEntries
    
    If rng > 0 Then
        dt = Format(CDate(CDbl(timestamps(i - 1)) / 86400 + DateSerial(1970, 1, 1)), "yyyy-mm-dd hh:mm:ss") & " " & "UTC"
    Else
        dt = Format(CDate(CDbl(timestamps(i - 1)) / 86400 + DateSerial(1970, 1, 1)), "yyyy-mm-dd")
    End If
    
        combinedData(i, 1) = dt
        combinedData(i, 2) = HandleNulls(openPrices(i - 1)) ' Open price
        combinedData(i, 3) = HandleNulls(highPrices(i - 1)) ' High price
        combinedData(i, 4) = HandleNulls(lowPrices(i - 1))  ' Low price
        combinedData(i, 5) = HandleNulls(closePrices(i - 1)) ' Close price
        combinedData(i, 6) = HandleNulls(adjClosePrice(i - 1)) ' Adjusted close price
        combinedData(i, 7) = HandleNulls(volumes(i - 1)) ' Volume
    Next i
    
    OutputData.Select
    range("A1000000").End(xlUp).Offset(OffsetCounter, 0).Select
    Selection.Resize(UBound(combinedData, 1), UBound(combinedData, 2)).value = combinedData

    range("H1000000").End(xlUp).Offset(OffsetCounter, 0).Select
    Selection.Resize(UBound(combinedData, 1), 1).value = Symbols
    
    
End Sub

Function ExtractValue(ByVal json As String, ByVal startTag As String, ByVal endTag As String) As String
    Dim startPos As Long
    Dim endPos As Long
    Dim extractedValue As String
    
    startPos = InStr(1, json, startTag) + Len(startTag)
    endPos = InStr(startPos, json, endTag)
    
    If startPos > 0 And endPos > 0 Then
        extractedValue = Mid(json, startPos, endPos - startPos)
        ExtractValue = Trim(extractedValue)
    Else
        ExtractValue = ""
    End If
End Function

Function HandleNulls(value As String) As Double
    Dim localeDecimalSeparator As String
    localeDecimalSeparator = Mid(CStr(1 / 2), 2, 1) ' Get the system's decimal separator

    ' Replace the period with the locale's decimal separator
    value = Replace(value, ".", localeDecimalSeparator)

    If IsNumeric(value) Then
        HandleNulls = CDbl(value)
    Else
        HandleNulls = 0 ' Return 0 if the value is null, empty, or not numeric
    End If
End Function

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.

Post Comment 44 Responses to "How to Download Historical Stock Data to Excel for Free"
  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
  4. Hi Deepanshu, thanks a lot for your great macro! It seems Yahoo changed something recently. For a few days now, I no longer get the usual csv table in the variable "resultFromYahoo". I now get the following error message:
    "{"finance":{"result":null,"error":{"code":"unauthorized","description":"User is not logged in"}}}"
    Do you know if Yahoo requires now a user login? Is there any quick fix?
    Thanks and kind regards,
    Evariste

    ReplyDelete
    Replies
    1. I have fixed the code and the updated file is uploaded to the drive. Please check and let me know if it works for you. Thanks!

      Delete
    2. Hi Deepanshu, thanks a lot for your quick response. Unfortunaly, I get only zero values when I run the new version. I saw you replaced "https://query1.finance.yahoo.com/v7/finance/download/" by "https://query2.finance.yahoo.com/v8/finance/chart/". Isn't here any possibilty to get the same table as before? I confess I know nothing about queries on Yahoo but I was also using the previous result in another macro and I liked the previous output which was much more user friendly.
      Do you know if we can still get this previous output?

      Delete
    3. It's not just a change of the URL but also how data being stored in variables. Are you using my macro file or some modified version of the previous macro file? Are you getting only zero values in my macro file? If yes, which symbol you are using?

      Delete
    4. It's working fine at my end. It seems to be server issue as dates and volume data seem to be fetched correctly. We can't make changes in the previous url as it stopped working..

      Delete
    5. Ok, too bad but thanks again for your help anyway. Have a nice rest of day!

      Delete
    6. I made a few minor changes in the updated file. See if it works for you now.

      Delete
    7. Hi Deepanshu, thanks a lot for the follow up. It's still not working but I've identified why:
      1) For some reason "Dim openPrices() As String" doesn't dimension openPrices (and the other variables alike) as arrays. Therefore, when "openPrices = Split(ExtractValue(..." is executed it generates an error. Seems it can be fixed by "Dim openPrices" instead.
      2) I'm based in Europe and the decimal separator is "," and not ".", so in function HandleNulls, the line "If IsNumeric(value) Then" results in an error i.e. "160.254" is not considered as a number. This is why all values are nil except the Date and the Volume (which is not decimal). I guess I need to replace "." by "," beforehand. I'll work on that this week-end ;-).
      Kind regards, Évariste

      Delete
    8. Your second point seems convincing but I couldn't replicate the issue. I tried changing the system's regional settings and using a VPN for Europe but I'm still not getting zeros. It still worked for me. When you use Debug.Print resultFromYahoo, what JSON are you seeing in the immediate window? Also if you remove HandleNulls, did you have any success? (Try with only the latest date, so there's no chance of a null value from the source data)

      Delete
    9. Also try with this updated function and see if it works --
      Function HandleNulls(value As String) As Double
      Dim localeDecimalSeparator As String
      localeDecimalSeparator = Mid(CStr(1 / 2), 2, 1)

      ' Replace the period with the locale's decimal separator
      value = Replace(value, ".", localeDecimalSeparator)

      If IsNumeric(value) Then
      HandleNulls = CDbl(value)
      Else
      HandleNulls = 0 ' Return 0 if the value is null, empty, or not numeric
      End If
      End Function

      Delete
    10. Thanks a lot for your support, it works well now!
      Thanks again and kind regards,
      Évariste

      Delete
    11. Awesome. Just wanted to double check the updated function HandleNulls works for you..

      Delete
    12. Yes, the HandleNulls function works fine now 👍, thanks!

      Delete
  5. Hi Deepanshu, while downloading NSE data I am getting error code Subscript Out of Range error. If I click on debug it's stopped at "ReDim combinedData(1 To numberOfEntries, 1 To nColumns)" line. Only first stock data from list was loaded and then this error is coming. Please check and help.

    ReplyDelete
    Replies
    1. What starting and end date you used in the file? What symbols are you using?

      Delete
    2. Is it working for multiple US Stocks at your end?

      Delete
    3. I don't provide support that's beyond the code mentioned in the article

      Delete
  6. Hi Deepanshi - does your macro have (or can it be modified) to download dividend info from Yahoo after the recent change in Sept that Yahoo made?

    ReplyDelete
    Replies
    1. No, dividend information is not stored in this API. It's a separate API. What exactly dividend information do you want?

      Delete
    2. Similar to historical stock prices that you have, allow the user to enter a symbol, start and end date and return all the dividend payouts for the dates for that symbol. see this screen on Yahoo
      https://finance.yahoo.com/quote/MSFT/history/?period1=1695158409&period2=1726780809&interval=capitalGain%7Cdiv%7Csplit&filter=div&frequency=1d&includeAdjustedClose=true

      Delete
    3. Got it. It's in the same API only. I will work on it tomorrow and update you. I will create a new post for this as it will be confusing to include dividend in this macro.

      Delete
    4. thanks Deepanshu. Looking forward to it - suspect your spreadsheet/macro will get fantastic usage as many people look to download historic dividend info. I certainly will find it incredibly helpful. Thank you in advance !!

      Delete
    5. I have prepared the macro. Check out this link - https://www.listendata.com/2024/09/how-to-import-dividend-data-into-excel.html

      Delete
  7. And for clarity, allow the user to enter a list of symbols (by way of a table), and have the API return all dividends for all the symbols in the table for the given time period.

    ReplyDelete
  8. Hi Deepanshu thank you very much for your code working perfect.
    But when i am trying to implement (just change url) in my sheet its giving no error and result blank. if possible can you check code and modify please as i dont have any codeing knowlage below code also i pickup from internet only and did my required changes in excel file and if i want to share my excel vba file please suggest the option. Saying comment is too long

    ReplyDelete
    Replies
    1. The old URL you were using no longer works. Use the macro file mentioned in the article.

      Delete
    2. used new URL https://query2.finance.yahoo.com/v8/finance/chart/ still result is blank. Please if u allow i'll share my VBA excel file link (wetransfer)

      Delete
    3. Each URL (API) has a different structure and can't be as simple as just replacing the URL. I don't provide support that's beyond the code mentioned in the article.

      Delete
  9. Hi Deepanshu,

    Just a quick pointer:

    StartPos will always be > 0, as you have added Len(startTag)

    -----------------

    Function ExtractValue(ByVal json As String, ByVal startTag As String, ByVal endTag As String) As String
    Dim startPos As Long
    Dim endPos As Long
    Dim extractedValue As String

    startPos = InStr(1, json, startTag) + Len(startTag) ' <<<<<<<<<<<<<<<<<<<<<<<
    endPos = InStr(startPos, json, endTag)

    If startPos > 0 And endPos > 0 Then
    extractedValue = Mid(json, startPos, endPos - startPos)
    ExtractValue = Trim(extractedValue)
    Else
    ExtractValue = ""
    End If
    End Function

    ReplyDelete
  10. Hi Deepanshu, your macro works perfectly fine on Windows but there's an error when running on Mac. I know you don't provide support on iOS but maybe you can help me searching in the right direction. In sub "getCookieCrumb", I get an error at line:
    Set objRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    The error message is:
    Run-time error 429: ActiveX component can't create object
    Any idea what I could do to fix it?

    ReplyDelete
    Replies
    1. The macro can run on both Windows and macOS. Download the latest file. Please check and confirm the same. Thanks!

      Delete
    2. Hi Deepanshu, thanks for the update. The latest macro seems to work on macOS. However, the VBA code is password protected and I'd like to access to it (I made some slight adjustments to the previous version). Would you mind removing the password? Or sending it to me in a separate email (gsdl@free.fr)? Thanks!

      Delete
    3. Thanks a lot Deepanshu. Have a nice day!

      Delete
    4. Hi Deepanshu, it seems I'm unlucky these days... Your macro worked fine last week but now I get an error message box on Mac:
      "microsoft visual basic - Internal error"
      Any idea why it doesn't work? I can't even compile the VB code. I'm on Mac OS Ventura 13.7 and MS Office 365 on Mac.
      (It still work on Windows.)

      Delete
    5. I don't know what's causing this issue. It seems related to the recent updates. Contact Microsoft support team.

      Delete
Next → ← Prev