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.
- Go to the folder where the downloaded macro file is located.
- Right-click the file and choose Properties from the menu.
- At the bottom of the General tab, select the Unblock checkbox under security option and then click OK.
- Open the file.
- Enter the tickers (symbols) of the stocks for which you want data, starting from cell B6.
- 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).
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).
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).
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 fromDateAdd("s", unixTimestamp, epoch)toDateAdd("s", unixTimestamp - 14400, epoch)
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.
Share Share Tweet