Get Pre & Post Market Stock Data in Excel

Deepanshu Bhalla Add Comment

This article explains how to pull pre and post stock market data in Excel for free.

I have created an Excel macro that imports data of pre-market and post-market hours from Yahoo Finance into your Excel. This tool is ideal for traders who want to track market outside of regular trading hours. It allows them to make informed trades when the market opens or to take positions during extended hours.

How to Use Macro

To resolve the following error when opening the excel file, follow the steps below -

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.
  5. Enter the tickers (symbols) of the stocks for which you want data, starting from cell B6.
  6. Press the "Get Data From YahooFinance" button to import data for each stock.
Pre-Market Data

The macro returns the following metrics of pre-market data -

  • Pre-Market Price : Stock price before the market opens.
  • Pre-Market Change % : Percentage change in stock price from the previous closing price during pre-market hours.
  • Pre-Market Change : Absolute change in stock price from the previous close during pre-market hours.
  • Pre-Market Time (UTC) : Timestamp for the latest pre-market update in Coordinated Universal Time (UTC).
Download Pre Market Stock Data in Excel
Post-Market Data

The macro returns the following metrics of post-market data -

  • Post-Market Price : Stock price after the market has closed.
  • Post-Market Change % : Percentage change in stock price from the closing price during post-market hours.
  • Post-Market Change : Absolute change in stock price from the close during post-market hours.
  • Post-Market Time (UTC) : Timestamp for the latest post-market update in Coordinated Universal Time (UTC).
Download Post Market Stock Data in Excel
Market Data

The macro returns the following metrics of live data of stock market -

  • Price : Current stock price during regular trading hours.
  • Change % : Percentage change in stock price from the previous closing price.
  • Change : Absolute change in stock price from the previous close.
  • High : Highest price reached during the trading session.
  • Low : Lowest price reached during the trading session.
  • Volume : Total number of shares traded during the session.
  • Avg Vol. (3M) : Average trading volume over the past three months.
  • Time (UTC) : Timestamp for the most recent stock price update in Coordinated Universal Time (UTC).
Download Live Market Stock Data in Excel

For non-US stocks, you need to input abbreviation of the exchange as a suffix in tickers used by Yahoo Finance. Refer to the table below showing different markets along with their corresponding exchanges.

Example : The ticker for Unilever is ULVR. So we need to input ULVR.L as 'L' refers to the exchange in UK.
Market Exchange 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

Follow the instructions below to enter the code in your excel file.

  • In Excel, click on "Visual Basic" under the developer tab or press Alt + F11 to open the VBA editor.
  • In the VBA editor, right-click on any of the items in the Project Explorer window, select "Insert" and then choose "Module".
  • Paste VBA code in the module and then close the VBA editor.
  • Run your macro by clicking on the "Macros" button in the Developer tab or press Alt + F8 and then select your macro and click "Run"

Sub GetYahooFinanceData()
    
    Dim symbol As String
    Dim startRow As Long, startCol As Long, j As Long
    Dim InputControls As Worksheet
    Dim nColumns As Integer
    Dim combinedData() As Variant
    Dim tempData() As Variant
    Dim response As String
    Dim url As String
    Dim httpRequest As Object
    Dim html As Object
    Dim scriptNodes As Object
    Dim scriptText As String
    Dim i As Integer
    Dim foundIndex As Integer

    Set httpRequest = CreateObject("MSXML2.XMLHTTP")
    Set html = CreateObject("HTMLFile")

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
        
   'Last Row
    startRow = 6
    startCol = 2
    Set InputControls = Sheets("YahooFinance")
    With InputControls
        last = .Cells(.Rows.Count, startCol).End(xlUp).Row
    End With

    ' Array
    numberOfEntries = 1
    nColumns = 16
    ReDim combinedData(1 To numberOfEntries, 1 To nColumns)

    ' Clear previous content
    ActiveSheet.Range("C6:R100000").ClearContents
    
    ' Loop starts here
    For j = startRow To last
    startRow = j
    symbol = InputControls.Cells(startRow, startCol).Value

    ' Build the URL
    url = "https://finance.yahoo.com/quote/" & symbol & "/profile"
    
    ' Make the HTTP request
    With httpRequest
        .Open "GET", url, False
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.95 Safari/537.36"
        .send
    End With
    
    ' Check for HTTP errors
    If httpRequest.Status > 200 Then
        MsgBox "Error Code: " & httpRequest.Status
        Exit Sub
    End If
    
    ' Parse the response
    response = httpRequest.responseText
    
    ' Extract script content
    html.Body.innerHTML = response
    
    Set scriptNodes = html.getElementsByTagName("script")
    foundIndex = -1
       
    For i = scriptNodes.Length - 1 To 0 Step -1
        scriptText = scriptNodes(i).innerText
        If InStr(scriptText, "summaryDetail") > 0 Then
            foundIndex = i
            Exit For
        End If
    Next i
    
    If foundIndex = -1 Then
        MsgBox "No 'summaryDetail' found in scripts."
        Exit Sub
    End If
    
    ' Remove any backslashes from the script text
    scriptText = Replace(scriptText, "\", "")
   
    'Pre Market
    preMarketPrice = ExtractValue(scriptText, "preMarketPrice""")
    preMarketChangePercent = ExtractValue(scriptText, "preMarketChangePercent""")
    preMarketChange = ExtractValue(scriptText, "preMarketChange""")
    preMarketTime = ExtractValue(scriptText, "preMarketTime""")
    
    'Regular Hours
    regularMarketPrice = ExtractValue(scriptText, "regularMarketPrice""")
    regularMarketChangePercent = ExtractValue(scriptText, "regularMarketChangePercent""")
    regularMarketChange = ExtractValue(scriptText, "regularMarketChange""")
    regularMarketHigh = ExtractValue(scriptText, "regularMarketDayHigh""")
    regularMarketLow = ExtractValue(scriptText, "regularMarketDayLow""")
    regularMarketVolume = ExtractValue(scriptText, "regularMarketVolume""")
    averageDailyVolume3Month = ExtractValue(scriptText, "averageDailyVolume3Month""")
    regularMarketTime = ExtractValue(scriptText, "regularMarketTime""")
    
    'Post Market
    postMarketPrice = ExtractValue(scriptText, "postMarketPrice""")
    postMarketChangePercent = ExtractValue(scriptText, "postMarketChangePercent""")
    postMarketChange = ExtractValue(scriptText, "postMarketChange""")
    postMarketTime = ExtractValue(scriptText, "postMarketTime""")
                 
        combinedData(numberOfEntries, 1) = preMarketPrice
        combinedData(numberOfEntries, 2) = preMarketChangePercent
        combinedData(numberOfEntries, 3) = preMarketChange
        combinedData(numberOfEntries, 4) = UnixToDateTime(preMarketTime)
        
        combinedData(numberOfEntries, 5) = regularMarketPrice
        combinedData(numberOfEntries, 6) = regularMarketChangePercent
        combinedData(numberOfEntries, 7) = regularMarketChange
        combinedData(numberOfEntries, 8) = regularMarketHigh
        combinedData(numberOfEntries, 9) = regularMarketLow
        combinedData(numberOfEntries, 10) = regularMarketVolume
        combinedData(numberOfEntries, 11) = averageDailyVolume3Month
        combinedData(numberOfEntries, 12) = UnixToDateTime(regularMarketTime)
        
        combinedData(numberOfEntries, 13) = postMarketPrice
        combinedData(numberOfEntries, 14) = postMarketChangePercent
        combinedData(numberOfEntries, 15) = postMarketChange
        combinedData(numberOfEntries, 16) = UnixToDateTime(postMarketTime)
   
        currentRow = ActiveSheet.Range("C1000000").End(xlUp).Offset(1, 0).Row
        ActiveSheet.Cells(currentRow, 3).Resize(numberOfEntries, nColumns).Value = combinedData
   
    Next j
    
    Application.Calculation = xlCalculationAutomatic

End Sub

Function ExtractValue(ByVal scriptText As String, ByVal Key As String) As String
    Dim keyPosition As Long
    Dim rawStart As Long, fmtStart As Long, fmtEnd As Long, rawEnd As Long
    Dim Value As String
    
    keyPosition = InStr(1, scriptText, Key) + Len(Key)
    If (keyPosition - Len(Key)) > 0 Then
        
        fmtStart = InStr(keyPosition, scriptText, "{") - keyPosition
        fmtEnd = InStr(keyPosition, scriptText, "}") - keyPosition
        
        If (fmtStart < 4) And (fmtEnd - fmtStart > 1) Then
            rawStart = InStr(keyPosition, scriptText, "fmt") + 6
            rawEnd = InStr(rawStart, scriptText, """")
        Else
            rawStart = keyPosition + 1
            rawEnd = InStr(rawStart, scriptText, ",")
        End If
        Value = Mid(scriptText, rawStart, rawEnd - rawStart)
        
        If Value = "{}" Then
            Value = "NA"
        End If
        
    Else
        Value = "NA"
    End If
    
    ExtractValue = Value
End Function

Function UnixToDateTime(ByVal unixTimestamp As Variant) As Variant
    If IsNumeric(unixTimestamp) Then
        Dim epoch As Date
        epoch = #1/1/1970#
    
        ' Add the Unix timestamp (seconds) to the epoch date
        UnixToDateTime = DateAdd("s", unixTimestamp - 14400, epoch)
    Else
        UnixToDateTime = unixTimestamp
    End If
End Function
How to Convert Time From UTC to Local Time

To convert a Unix timestamp from UTC to local time, you need to adjust for the time difference between UTC and local timezone.

For example, for the time difference between UTC and Eastern Daylight Time (EDT). EDT is UTC-4 hours, so you will need to subtract 14,400 seconds (4 hours × 60 minutes × 60 seconds) from the UTC time. Make the following changes in the code.

Change this line of code from DateAdd("s", unixTimestamp, epoch) to DateAdd("s", unixTimestamp - 14400, epoch)
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 0 Response to "Get Pre & Post Market Stock Data in Excel"
Next → ← Prev