How to Download Historical Stock Data to Excel for Free

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.

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".

As shown in the image below, we have stock data with a five-minute time frame presented in tabular format 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 Share Tweet

About Author:

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.

While I love having friends who agree, I only learn from those who don't
Let's Get Connected Email LinkedIn