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.
- 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.
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.
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.
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 |
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(/