This article explains how to fetch pre and post stock market data in Google Sheets for free.
I have created an app script that imports data of pre-market and post-market hours from Yahoo Finance into your Google Sheets. It helps traders in tracking market outside of regular trading hours.
How to Use the File :
- First make a copy by selecting File > Make a copy.
- While running the script, Google will ask you to authorize Apps Script once. Don't worry, it's a standard prompt that appears for every user when a script is used.
- Enter tickers (symbols) starting from cell B6 and then hit the "Get Data From Yahoo Finance" button.
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 : Timestamp for the latest pre-market update in local timezone.
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 local timezone.
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 local timezone.
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 Shell is SHEL. So we need to input SHEL.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 |
Google Sheets Apps Script
Follow the instructions below to enter the script in your google sheet.
- In your Google Sheet, click on Extensions > Apps Script.
- Delete the default function and paste your Apps Script code.
- Save the script with Ctrl + S or the floppy disk icon.
- Close the Apps Script editor.
- Insert a button by clicking Insert > Drawing > New.
- Assign the script to the button by selecting Assign script and entering the function name.
- Run the script and authorize it when prompted.
function getYahooFinanceData() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("YahooFinance");
var startRow = 6;
var startCol = 2;
var lastRow = sheet.getLastRow();
var nColumns = 16;
// Clear previous content
sheet.getRange("C6:R" + sheet.getMaxRows()).clearContent();
// Loop through each symbol
for (var j = startRow; j <= lastRow; j++) {
var symbol = sheet.getRange(j, startCol).getValue();
if (symbol) {
var url = "https://finance.yahoo.com/quote/" + symbol + "/profile";
try {
var response = UrlFetchApp.fetch(url, {
"headers": {
"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"
}
});
var html = response.getContentText();
// Check for HTTP errors
if (html.indexOf("summaryDetail") == -1) {
Logger.log("Error: No 'summaryDetail' found in the html for symbol " + symbol);
continue;
}
var scriptText = extractScriptContent(html, "summaryDetail");
// Ensure we found the required script
if (!scriptText) {
Logger.log("No 'summaryDetail' found for symbol: " + symbol);
continue;
}
// Clean and parse the JSON data
scriptText = cleanScriptText(scriptText);
var jsonData;
try {
jsonData = JSON.parse(scriptText);
} catch (e) {
Logger.log("Error parsing JSON for symbol: " + symbol);
continue;
}
// Extract the body content and relevant market data
var bodyContent = JSON.parse(jsonData.body);
var marketData = bodyContent.quoteSummary.result[0].price;
// Extract Pre-market data
var preMarketPrice = marketData.preMarketPrice ? marketData.preMarketPrice.fmt : "NA";
var preMarketChangePercent = marketData.preMarketChangePercent ? marketData.preMarketChangePercent.fmt : "NA";
var preMarketChange = marketData.preMarketChange ? marketData.preMarketChange.fmt : "NA";
var preMarketTime = marketData.preMarketTime ? formatUnixTimestamp(marketData.preMarketTime) : "NA";
// Extract Regular market data
var regularMarketPrice = marketData.regularMarketPrice ? marketData.regularMarketPrice.fmt : "NA";
var regularMarketChangePercent = marketData.regularMarketChangePercent ? marketData.regularMarketChangePercent.fmt : "NA";
var regularMarketChange = marketData.regularMarketChange ? marketData.regularMarketChange.fmt : "NA";
var regularMarketHigh = marketData.regularMarketDayHigh ? marketData.regularMarketDayHigh.fmt : "NA";
var regularMarketLow = marketData.regularMarketDayLow ? marketData.regularMarketDayLow.fmt : "NA";
var regularMarketVolume = marketData.regularMarketVolume ? marketData.regularMarketVolume.fmt : "NA";
var regularAvgVolume = marketData.averageDailyVolume3Month ? marketData.averageDailyVolume3Month.fmt : "NA";
var regularMarketTime = marketData.regularMarketTime ? formatUnixTimestamp(marketData.regularMarketTime) : "NA";
// Extract Post-market data
var postMarketPrice = marketData.postMarketPrice ? marketData.postMarketPrice.fmt : "NA";
var postMarketChangePercent = marketData.postMarketChangePercent ? marketData.postMarketChangePercent.fmt : "NA";
var postMarketChange = marketData.postMarketChange ? marketData.postMarketChange.fmt : "NA";
var postMarketTime = marketData.postMarketTime ? formatUnixTimestamp(marketData.postMarketTime) : "NA";
// Insert data into the sheet
sheet.getRange(j, startCol+1, 1, nColumns).setValues([[preMarketPrice, preMarketChangePercent, preMarketChange, preMarketTime,
regularMarketPrice, regularMarketChangePercent, regularMarketChange, regularMarketHigh,
regularMarketLow, regularMarketVolume, regularAvgVolume,regularMarketTime, postMarketPrice, postMarketChangePercent, postMarketChange, postMarketTime]]);
} catch (error) {
Logger.log("Error fetching data for " + symbol + ": " + error);
}
}
}
}
// Function to extract script content
function extractScriptContent(html, keyword) {
var scriptTags = html.match(/]*>([\s\S]*?)<\/script>/gi);
for (var i = scriptTags.length - 1; i >= 0; i--) {
if (scriptTags[i].indexOf(keyword) > -1) {
return scriptTags[i];
}
}
return "";
}
// Function to clean up the script content
function cleanScriptText(scriptText) {
// Find the part where JSON starts, usually after "root.App.main = " or similar
var jsonStart = scriptText.indexOf('{');
var jsonEnd = scriptText.lastIndexOf('}');
// Extract the JSON part and remove any trailing semicolons or variable declarations
if (jsonStart !== -1 && jsonEnd !== -1) {
return scriptText.substring(jsonStart, jsonEnd + 1);
}
return null;
}
// Function to format Unix timestamp into human-readable date and time
function formatUnixTimestamp(unixTimestamp) {
// Get the local timezone of the script
var timeZone = Session.getScriptTimeZone();
// Create a Date object for the Unix timestamp
var date = new Date(unixTimestamp * 1000); // Convert seconds to milliseconds
// Format the date to the local timezone
var options = {
timeZone: timeZone,
year: 'numeric',
month: '2-digit',
day: '2-digit',
hour: '2-digit',
minute: '2-digit',
second: '2-digit',
hour12: false // Change to true for 12-hour format
};
// Format the date using Intl.DateTimeFormat
var formatter = new Intl.DateTimeFormat('en-US', options);
return formatter.format(date);
}
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