I am pulling stock market data from Yahoo! Finance. For data that does not change often, I do not want to contact Yahoo! each time, but source data from a db sheet that acts as a cache:
yahoofinance() is called per ticker from 'Dashboard' sheetyahoofinance() calls db_data to see if data for ticker is available in dbI have an example sheet here.
function yahoofinance(ticker) {
if (db_data(ticker)) {
return get_db_data(ticker);
}
else {
return get_live_data(ticker);
}
}
function get_live_data(ticker) {
const url = 'https://query2.finance.yahoo.com/v10/finance/quoteSummary/' + encodeURI(ticker) + '?modules=price,assetProfile,summaryDetail';
let response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
if (response.getResponseCode() == 200) {
var object = JSON.parse(response.getContentText());
}
let fwdPE = object.quoteSummary.result[0]?.summaryDetail?.forwardPE?.fmt || '-';
let sector = object.quoteSummary.result[0]?.assetProfile?.sector || '-';
let mktCap = object.quoteSummary.result[0]?.price?.marketCap?.fmt || '-';
return [[fwdPE, sector, mktCap]];
}
function db_data(key) {
var db = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('db');
var tickers = db.getDataRange('A2:A').getValues();
for (var r = 0; r <= tickers.length; r++) {
if (tickers[r] == key) {
return true // found a row with this key
}
else {
return false // no row with this key exists
}
}
}
Issue: db_data() does not find ticker AAPL (see sheet Dashboard) even though it is present in sheet db so the matching in db_data() does not work. What am I doing wrong?
A working version of the script with the changes suggested in the comments:
function yahoofinance(ticker) {
if (db_data(ticker)) {
return db_data(ticker);
}
else {
return get_live_data(ticker);
}
}
function get_live_data(ticker) {
const url = 'https://query2.finance.yahoo.com/v10/finance/quoteSummary/' + encodeURI(ticker) + '?modules=price,assetProfile,summaryDetail';
let response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
if (response.getResponseCode() == 200) {
var object = JSON.parse(response.getContentText());
}
let fwdPE = object.quoteSummary.result[0]?.summaryDetail?.forwardPE?.fmt || '-';
let sector = object.quoteSummary.result[0]?.assetProfile?.sector || '-';
let mktCap = object.quoteSummary.result[0]?.price?.marketCap?.fmt || '-';
return [[fwdPE, sector, mktCap]];
}
function db_data(key) {
var db = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('db');
var tickers = db.getRange('A2:A').getValues();
for (var r = 0; r <= tickers.length; r++) {
if (tickers[r] == key) {
return true // found a row with this key
}
else {
return false // no row with this key exists
}
}
}
Results: