Get Pre & Post Market Stock Data in Google Sheets

Deepanshu Bhalla

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.
Import Pre Market Stock Data in Google Sheets
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.
Download Post Market Stock Data in Google Sheets
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.
Download Live Market Stock Data in Google Sheets

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);
}
Related Posts
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 Google Sheets"