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.
To resolve the following error when opening the excel file, follow the steps below -
- 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.
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).
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).
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).
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 |
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
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)
Share Share Tweet