Alternatives to google finance stock prices

user_15176925 0
Viewed 655

I'm trying to make a Stock portfolio tracker for my investments. I already understand how to use google finance but the 20 min delay on prices is a drag. I've updated the recalculation for my sheets to on change and every minute but the delay will still happen. So my real question is if and how could I pull a stock price from the Nasdaq into google sheets for a more real time feel.

google-apps-script google-sheets
2 Answers

What you can try is having script and have it triggered every minute.

I tried fetching from NASDAQ but it takes too long. I used WSJ instead but the issue is it randomly errors out due to something I was never able to point out. Thus I added a column to identify when the data was last updated. Don't worry though, it will be updated within the next run.

Code:

function getPrice() {
  var sheet = SpreadsheetApp.getActiveSheet(); 
  // tickers will be checked on column A starting 2nd row
  var values = sheet.getRange("A2:A" + sheet.getLastRow()).getValues().flat();
  sheet.getRange("A1:C1").setValues([["Tickers", "Price", "Last Updated"]]);
  values.forEach(function (ticker, index){
    var url = "https://www.wsj.com/market-data/quotes/" + ticker;
    Utilities.sleep(2000);
    var html = UrlFetchApp.fetch(url).getContentText();
    Utilities.sleep(2000);
    var price = html.match(/quote_val">([\d ,.]+)/);
    // since it randomly errors out on [1], check if price is null
    // add date and time too on column C to confirm when was the data last updated
    if(price){
      sheet.getRange(index + 2, 2).setValue(price[1].trim());
      sheet.getRange(index + 2, 3).setValue(new Date());
    }
  });
  // call to remove existing triggers and create another one
  createTrigger();
}

function createTrigger(){
  // Delete all existing triggers before creating one
  // Ensuring none will exist before creating trigger.
  var triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(function (trigger){
    ScriptApp.deleteTrigger(trigger);
  });

  // Create trigger after every run which is per minute
  ScriptApp.newTrigger('getPrice')
    .timeBased()
    .after(60 * 1000)
    .create();
}

Sample Output:

sample output

Note:

  • There are 1440 minutes in a day, and quota for Url Fetch calls is 20000 a day, so you must only have at most 13 tickers as when you reach the quota, it will not behave as expected. If you want more tickers, you need to adjust the frequency. (e.g. update every 2 minutes will allow you to have at most 27 tickers)

Resource:

user_161186860

GOOGLEFINANCE recalculation is set to 20 minutes and cant be re-set

you will need to either scrape it from somewhere (yahoo finance, coinmarketcap) or like MK mentioned you will need to pay for (API) it because free plans may not be enough for you. see: https://coinmarketcap.com/api/pricing/

user_56326290
Related