How to make Google spreadsheet refresh itself everyday (same day)?

Question

My google spreadsheet is using the function:

=GOOGLEFINANCE(TICKER,"all",DATE(yyyy/mm/dd),DATE(yyyy/mm/dd),"DAILY")

to retrieve stock prices every day of my portfolio.

Unfortunately, it is showing one day delay data, how can I gate same day data in my Google sheet?

Answer 1

You can use Google Apps Script to create a time-driven trigger that runs your desired function every day. In this case, the trigger to create would be everyDays, and the provided parameter would be 1.

You could create that trigger manually with the script editor, as shown here, or programmatically, through the use of the Script service, by running a function like this one:

function createDailyTrigger() {
  ScriptApp.newTrigger("callGoogleFinance")
    .timeBased()
    .everyDays(1)
    .create();
}

Then, you can use the function that is triggered each day (I named it callGoogleFinance) to call GOOGLEFINANCE by using setFormula in your cell and providing your formula as a parameter:

function callGoogleFinance() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("your-sheet-name"); // Please provide your sheet name here
  var cell = sheet.getRange("your-range"); // Please provide the row and column of your cell here 
  cell.setFormula("=GOOGLEFINANCE(TICKER,\"all\",DATE(yyyy/mm/dd),DATE(yyyy/mm/dd)");
}

I hope this is of any help.

Answer 2

GOOGLEFINANCE fetches current or historical securities information from Google Finance. If any date parameters are specified, the request is considered historical. But the current date is apparently not treated as historical in your case. It seem like GOOGLEFINANCE fetches information from and including start_date, up to but excluding end_date. To include end_date, you need to add one day.

If end_date is TODAY(), adding one day doesn't help. To make sure you get current day data you could try adding GOOGLEFINANCE without the date parameter.

={
GOOGLEFINANCE("GOOG","price", TODAY()-30, GOOGLEFINANCE("GOOG", "tradetime"));
{ 
GOOGLEFINANCE("GOOG", "tradetime"), 
GOOGLEFINANCE("GOOG", "priceopen")
} }

GOOGLEFINANCE("GOOG", "tradetime") as parameter, is to avoid duplicate dates when TODAY() is Saturday or Sunday.