/**
* Trading Agent Data for Google Sheets.
*
* =PIT("2330.TW", "revenue", 2024) FY2024 revenue
* =PIT("AAPL", "revenue", 2021, "2022-01-01") from filings dated on or before then
* =PIT("2330.TW", "revenue") the latest figure (may be one quarter)
* =PIT_HISTORY("2330.TW", "revenue", 5) five fiscal years, spilled
* =PIT_PERIOD(...) =PIT_FILED(...) which period; when it was filed
* =PIT_TABLE("AAPL") every figure, labelled, with its notices
* =PIT_SOURCES("2330.TW") the notices to keep with the figures
* =PIT_SEARCH("samsung") find an ID
*
* This file is generated by sdk/sheets/build.mjs from src/. Edit src/, not Code.gs.
*
* Install: Extensions > Apps Script, paste this file, name the project Trading Agent
* Data (the menu takes the project's name), Save, reload the sheet, then
* Extensions > Trading Agent Data > Open, and allow it once. Google only asks in
* response to a menu click: a formula cannot ask. Details: sdk/sheets/README.md.
*
* Your key stays in your own user properties. The notices each market's licence
* requires are written to a tab named Sources, by this add-on, as figures arrive.
*/
var TA_KEY_PROPERTY = 'TA_API_KEY';
var TA_SRC_PREFIX = 'ta_src_';
var TA_LIC_PROPERTY = 'ta_licences';
/** A property holds 9 kB; a notice in CJK runs to about 3 bytes a character. */
var TA_SRC_CHUNK = 2500;
/** The caller's own key; otherwise the public demo key, so a first formula works. */
function taKey_() {
try {
var own = PropertiesService.getUserProperties().getProperty(TA_KEY_PROPERTY);
if (own) return own;
} catch (e) { /* no user properties in this context */ }
try {
// Older installs kept the key here.
var old = PropertiesService.getScriptProperties().getProperty(TA_KEY_PROPERTY);
if (old) return old;
} catch (e) { /* as above */ }
return 'demo';
}
/** A digest, so neither a key nor its reply can be read out of a cache key. */
function taDigest_(s) {
return Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, s, Utilities.Charset.UTF_8)
.map(function (b) { return ('0' + (b & 255).toString(16)).slice(-2); })
.join('');
}
function taCache_() {
try {
return CacheService.getDocumentCache() || CacheService.getScriptCache();
} catch (e) {
return null;
}
}
function taCacheGet_(cache, id) {
if (!cache) return null;
try {
var hit = cache.get(id);
if (!hit) return null;
var blob = Utilities.newBlob(Utilities.base64Decode(hit), 'application/x-gzip');
return JSON.parse(Utilities.ungzip(blob).getDataAsString('UTF-8'));
} catch (e) {
return null;
}
}
function taCachePut_(cache, id, out) {
if (!cache) return;
try {
var gz = Utilities.gzip(Utilities.newBlob(JSON.stringify(out), 'application/json'));
cache.put(id, Utilities.base64Encode(gz.getBytes()), 21600); // 6 hours
} catch (e) {
// Above the 100 kB a cache entry may hold: it simply is not cached.
}
}
function taExplainPermission_(e) {
var msg = String((e && e.message) || e);
if (/permission/i.test(msg)) {
return new Error('Allow the add-on once: Extensions > Trading Agent Data > Open (the menu carries the name of the Apps Script project). A formula cannot ask Google for permission by itself.');
}
return e;
}
/**
* GET one API address with the caller's own key. Cached for 6 hours by key and
* address together: a file shared between people with different keys, or a
* published add-on with many users, must never hand one key's reply to another.
* Sheets runs a sheet's formulas at once, so a short lock lets the first one
* fetch and the rest read its reply, rather than all asking for the same company.
*/
function taFetchJson_(url) {
var key = taKey_();
var cache = taCache_();
var id = 'ta:' + taDigest_(key + '|' + url);
var hit = taCacheGet_(cache, id);
if (hit) return hit;
var lock = null;
try {
lock = LockService.getDocumentLock();
if (lock && !lock.tryLock(8000)) lock = null;
} catch (e) {
lock = null;
}
try {
hit = lock ? taCacheGet_(cache, id) : null;
if (hit) return hit;
var out = taRequest_(url, key, true);
taCachePut_(cache, id, out);
return out;
} finally {
if (lock) {
try { lock.releaseLock(); } catch (e) { /* released with the run */ }
}
}
}
function taRequest_(url, key, mayRetry) {
var res;
try {
res = UrlFetchApp.fetch(url, {
method: 'get',
muteHttpExceptions: true,
headers: { Authorization: 'Bearer ' + key }
});
} catch (e) {
throw taExplainPermission_(e);
}
var code = res.getResponseCode();
var body = null;
try {
body = JSON.parse(res.getContentText());
} catch (e) {
body = null;
}
if (code === 429 && mayRetry && body && body.retry_after_seconds <= 5) {
Utilities.sleep(body.retry_after_seconds * 1000);
return taRequest_(url, key, false);
}
if (code !== 200) throw new Error(PitCore.errorMessage(code, body));
if (!body) throw new Error('The API answered with something other than JSON.');
return PitCore.trim(body);
}
/** A company's reply, with its notices recorded for the Sources tab. Recorded
* on a cached reply too: the cache is per file, the record must be as well. */
function taCompany_(url, cutOff) {
var out = taFetchJson_(url);
out.as_of_given = cutOff;
taRecord_(out);
return out;
}
/* ---------- The record of notices: one per market, in the file itself ---------- */
function taStoreRead_() {
var store = PitCore.emptyStore();
var all = PropertiesService.getDocumentProperties().getProperties();
var parts = {};
Object.keys(all).forEach(function (k) {
if (k.indexOf(TA_SRC_PREFIX) === 0) parts[k.slice(TA_SRC_PREFIX.length)] = all[k];
});
Object.keys(parts).forEach(function (k) {
var m = /^([A-Z]+)\.0$/.exec(k);
if (!m) return;
var text = '';
for (var i = 0; parts[m[1] + '.' + i] !== undefined; i++) text += parts[m[1] + '.' + i];
store.markets[m[1]] = text;
});
if (all[TA_LIC_PROPERTY]) {
try { store.licences = JSON.parse(all[TA_LIC_PROPERTY]); } catch (e) { /* rewritten below */ }
}
return store;
}
function taRecord_(out) {
try {
var props = PropertiesService.getDocumentProperties();
var store = taStoreRead_();
var changed = PitCore.recordReply(store, out);
if (!changed.length) return;
var writes = {};
changed.forEach(function (m) {
if (m === 'licence') {
writes[TA_LIC_PROPERTY] = JSON.stringify(store.licences);
return;
}
var text = store.markets[m];
for (var i = 0; i * TA_SRC_CHUNK < text.length; i++) {
writes[TA_SRC_PREFIX + m + '.' + i] = text.slice(i * TA_SRC_CHUNK, (i + 1) * TA_SRC_CHUNK);
}
// A shorter notice must not keep the tail of a longer one.
for (var j = i; props.getProperty(TA_SRC_PREFIX + m + '.' + j) !== null; j++) {
props.deleteProperty(TA_SRC_PREFIX + m + '.' + j);
}
});
props.setProperties(writes);
} catch (e) {
// Never lose a figure over the record. The menu's "Update Sources tab"
// rebuilds it from the formulas, and every spilled block carries a pointer.
}
}
/** The tab, written only when it differs from the record. Runs from a simple
* trigger, so it may touch this file and nothing else. */
function taSyncTab_() {
var rows = PitCore.tabRows(taStoreRead_());
if (!rows.length) return false;
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(PitCore.TAB_NAME);
if (!sheet) {
var active = ss.getActiveSheet();
try {
sheet = ss.insertSheet(PitCore.TAB_NAME, ss.getNumSheets());
} catch (e) {
// Another trigger made it a moment ago.
sheet = ss.getSheetByName(PitCore.TAB_NAME);
if (!sheet) throw e;
}
// A new sheet takes the focus; the person was working somewhere else.
if (active) ss.setActiveSheet(active);
}
var current = sheet.getLastRow() ? sheet.getDataRange().getValues() : [];
if (JSON.stringify(current) === JSON.stringify(rows)) return false;
if (sheet.getLastRow()) sheet.getDataRange().breakApart();
sheet.clear();
// Plain text, so "2026-11-04" stays that text: read back as a date it would
// differ from the record, and the tab would be rewritten on every edit.
var range = sheet.getRange(1, 1, rows.length, 3);
range.setNumberFormat('@').setValues(rows).setWrap(true).setVerticalAlignment('top');
sheet.setColumnWidth(1, 70);
sheet.setColumnWidth(2, 90);
sheet.setColumnWidth(3, 900);
sheet.getRange(1, 1, 1, 3).merge().setFontWeight('bold');
sheet.getRange(2, 1, 1, 3).merge();
sheet.setRowHeight(2, 40); // A merged cell does not grow to fit its text.
sheet.getRange(3, 1, 1, 3).setFontWeight('bold');
sheet.setFrozenRows(3);
return true;
}
/** Tickers a formula names, with cells it points to read from its own sheet. */
function taTickersOf_(sheet, formula) {
var found = PitCore.tickersInFormula(formula);
var out = found.literal.slice();
found.refs.forEach(function (ref) {
try {
var v = sheet.getRange(ref).getValue();
if (v) out.push(String(v).trim().toUpperCase());
} catch (e) { /* not a cell */ }
});
return out;
}
/* ---------- Triggers and menu ---------- */
function onInstall(e) {
onOpen(e);
}
function onOpen(e) {
SpreadsheetApp.getUi()
.createAddonMenu()
.addItem('Open', 'taShowSidebar')
.addItem('Update Sources tab', 'taUpdateSourcesFromMenu')
.addToUi();
if (e && e.authMode === ScriptApp.AuthMode.NONE) return;
try { taSyncTab_(); } catch (err) { /* the next edit tries again */ }
}
/**
* An edit: bring the tab up to date. A formula just typed has not answered yet,
* and a formula cannot write to another tab, so when it names a market the record
* does not hold, wait here for its reply to arrive.
*/
function onEdit(e) {
if (!e || e.authMode === ScriptApp.AuthMode.NONE) return;
try {
var waiting = false;
var store = taStoreRead_();
if (e.range && e.range.getNumRows() === 1 && e.range.getNumColumns() === 1) {
waiting = taTickersOf_(e.range.getSheet(), e.range.getFormula()).some(function (t) {
var m = PitCore.marketOfTicker(t);
return !m || !store.markets[m];
});
}
taSyncTab_();
if (!waiting) return;
var before = Object.keys(store.markets).length;
for (var i = 0; i < 8; i++) {
Utilities.sleep(1500);
if (Object.keys(taStoreRead_().markets).length > before) {
taSyncTab_();
return;
}
}
} catch (err) {
// A trigger has nowhere to report to; the next edit or open tries again.
}
}
function taShowSidebar() {
SpreadsheetApp.getUi().showSidebar(
HtmlService.createHtmlOutput(TA_SIDEBAR_HTML).setTitle('Trading Agent Data')
);
}
/**
* The route that does not depend on a formula's reply: read every formula in the
* file, ask the API about each company named, and write the tab. Google has
* authorised this run, so it can do what a formula cannot.
*/
function taUpdateSources() {
var seen = {};
SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(function (sheet) {
if (sheet.getName() === PitCore.TAB_NAME || !sheet.getLastRow()) return;
sheet.getDataRange().getFormulas().forEach(function (row) {
row.forEach(function (formula) {
if (formula) taTickersOf_(sheet, formula).forEach(function (t) { seen[t] = true; });
});
});
});
var failures = [];
var byMarket = {};
Object.keys(seen).forEach(function (t) {
var m = PitCore.marketOfTicker(t);
if (m && byMarket[m]) return; // One reply gives a market's notice.
try {
taCompany_(PitCore.latestUrl(t, ''), '');
if (m) byMarket[m] = true;
} catch (e) {
failures.push(t + ': ' + e.message);
}
});
taSyncTab_();
return { companies: Object.keys(seen).length, failures: failures };
}
function taUpdateSourcesFromMenu() {
var r = taUpdateSources();
SpreadsheetApp.getActiveSpreadsheet().toast(
r.failures.length ? 'Sources updated, with problems: ' + r.failures.join(' | ').slice(0, 300) : 'Sources tab is up to date.',
'Trading Agent Data'
);
}
/* ---------- What the sidebar calls ---------- */
function taSidebarState() {
try { taSyncTab_(); } catch (e) { /* shown as stale until the next call */ }
var store = taStoreRead_();
return {
key: PitCore.maskKey(taKey_()),
own: taKey_() !== 'demo',
sources: PitCore.sourcesList(store),
licences: PitCore.licencesList(store)
};
}
/** Saved only once the API accepts it, so a typo shows the API's own words here
* rather than as an error in every cell. */
function taSaveKey(value) {
var key = PitCore.normaliseKey(value);
taRequest_(PitCore.latestUrl('AAPL', ''), key, false);
PropertiesService.getUserProperties().setProperty(TA_KEY_PROPERTY, key);
return taSidebarState();
}
function taClearKey() {
PropertiesService.getUserProperties().deleteProperty(TA_KEY_PROPERTY);
return taSidebarState();
}
/* ---------- Formulas ---------- */
function taSheetTimeZone_() {
try {
return SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
} catch (e) {
return Session.getScriptTimeZone();
}
}
/**
* A date cell arrives as midnight in the SPREADSHEET's time zone, so it is read in
* that zone: in UTC, 1 January in Paris or Auckland would be 31 December, and a
* filing dated that day would be left out.
*/
function taAsOf_(value) {
return PitCore.asOf(value, {
formatDate: function (d) { return Utilities.formatDate(d, taSheetTimeZone_(), 'yyyy-MM-dd'); }
});
}
function taFigure_(ticker, metric, period, as_of, basis) {
var t = PitCore.ticker(ticker);
var m = PitCore.metric(metric);
var p = PitCore.period(period);
var cutOff = taAsOf_(as_of);
var b = PitCore.basis(basis);
if (p) return PitCore.periodFact(taCompany_(PitCore.historyUrl(t, cutOff), cutOff), m, p, b);
if (b) return PitCore.latestOnBasis(taCompany_(PitCore.historyUrl(t, cutOff), cutOff), m, b);
return PitCore.latestFact(taCompany_(PitCore.latestUrl(t, cutOff), cutOff), m);
}
/**
* One filed figure. With a year, that fiscal year's figure; with a quarter such as
* "2024Q3", that quarter's; with neither, the latest (for US and Taiwan companies
* often one quarter: PIT_PERIOD says which).
*
* @param {string} ticker Company ID, e.g. "2330.TW", "AAPL", "387949530.FR".
* @param {string} metric Metric name, e.g. "revenue", "net_income", "assets".
* @param {number} period Optional fiscal year such as 2024, or a quarter such as "2024Q3".
* @param {string} as_of Optional date cell or "YYYY-MM-DD": only filings dated on or before it.
* @param {string} basis Optional "consolidated", "solo" or "reported". Default: the group figure where there is one.
* @return The figure.
* @customfunction
*/
function PIT(ticker, metric, period, as_of, basis) {
return taFigure_(ticker, metric, period, as_of, basis).value;
}
/**
* Which period PIT's figure covers: "FY2024" for a full year, "2024 Q3" for one
* quarter (the calendar quarter containing the period end).
*
* @param {string} ticker Company ID.
* @param {string} metric Metric name.
* @param {number} period Optional fiscal year or quarter.
* @param {string} as_of Optional date cell or "YYYY-MM-DD".
* @param {string} basis Optional basis.
* @return The period.
* @customfunction
*/
function PIT_PERIOD(ticker, metric, period, as_of, basis) {
return PitCore.factPeriod(taFigure_(ticker, metric, period, as_of, basis));
}
/**
* The filing date behind PIT's figure, as its market records it (what as_of
* filters on).
*
* @param {string} ticker Company ID.
* @param {string} metric Metric name.
* @param {number} period Optional fiscal year or quarter.
* @param {string} as_of Optional date cell or "YYYY-MM-DD".
* @param {string} basis Optional basis.
* @return The filing date, YYYY-MM-DD.
* @customfunction
*/
function PIT_FILED(ticker, metric, period, as_of, basis) {
return taFigure_(ticker, metric, period, as_of, basis).filed;
}
/**
* Several fiscal years of one metric, spilled down: year, value, unit, period end,
* filing date, form and basis, oldest first.
*
* @param {string} ticker Company ID.
* @param {string} metric Metric name.
* @param {number} years Optional number of most recent years. Blank: every year the key may read.
* @param {string} as_of Optional date cell or "YYYY-MM-DD": only filings dated on or before it.
* @param {string} basis Optional "consolidated", "solo" or "reported".
* @return A block of rows.
* @customfunction
*/
function PIT_HISTORY(ticker, metric, years, as_of, basis) {
var t = PitCore.ticker(ticker);
var m = PitCore.metric(metric);
var count = PitCore.yearCount(years);
var cutOff = taAsOf_(as_of);
var b = PitCore.basis(basis);
return PitCore.historyTable(taCompany_(PitCore.historyUrl(t, cutOff), cutOff), m, count, b);
}
/**
* Every figure of one company at its latest, labelled, then its source and licence
* notices.
*
* @param {string} ticker Company ID.
* @param {string} as_of Optional date cell or "YYYY-MM-DD".
* @return A block of rows.
* @customfunction
*/
function PIT_TABLE(ticker, as_of) {
var cutOff = taAsOf_(as_of);
return PitCore.latestTable(taCompany_(PitCore.latestUrl(PitCore.ticker(ticker), cutOff), cutOff));
}
/**
* The source and licence notices for a company's figures, in full. Use it to keep a
* notice on the page beside figures you show to others.
*
* @param {string} ticker Company ID.
* @return A block of rows.
* @customfunction
*/
function PIT_SOURCES(ticker) {
var rows = PitCore.noticeRows(taCompany_(PitCore.latestUrl(PitCore.ticker(ticker), ''), ''));
if (!rows.length) PitCore.fail('none', 'No notices: no figures for this ID.');
return PitCore.rect(rows);
}
/**
* Find a company's ID from its name, symbol or registration number.
*
* @param {string} query Name or number, e.g. "samsung", "2330".
* @return A block of rows: ticker, name, market.
* @customfunction
*/
function PIT_SEARCH(query) {
if (!query) PitCore.fail('value', 'query is required, for example "samsung".');
var url = PitCore.BASE + '/api/search' + PitCore.queryString({ q: String(query) });
return PitCore.searchTable(taFetchJsonRaw_(url));
}
/** Search answers in its own shape, with no key and no notices to record. */
function taFetchJsonRaw_(url) {
var res = UrlFetchApp.fetch(url, { method: 'get', muteHttpExceptions: true });
var body = null;
try { body = JSON.parse(res.getContentText()); } catch (e) { body = null; }
if (res.getResponseCode() !== 200) throw new Error(PitCore.errorMessage(res.getResponseCode(), body));
return body;
}
/* ---------- The first version's formulas, which sheets already use ---------- */
function taOld_(fn) {
try {
return fn();
} catch (e) {
return '#ERROR ' + e.message;
}
}
/**
* The latest figure, or the error as text. Earlier name for PIT.
*
* @param {string} ticker Company ID.
* @param {string} metric Metric name.
* @param {string} as_of Optional date cell or "YYYY-MM-DD".
* @return The figure.
* @customfunction
*/
function TA(ticker, metric, as_of) {
if (!ticker) return '#N/A ticker required';
if (!metric) return '#N/A metric required';
return taOld_(function () {
var cutOff = taAsOf_(as_of);
var f = taCompany_(PitCore.latestUrl(PitCore.ticker(ticker), cutOff), cutOff).data[String(metric).toLowerCase()];
return f ? f.value : '#N/A no ' + metric;
});
}
/**
* Which period TA's figure covers. Earlier name for PIT_PERIOD.
*
* @param {string} ticker Company ID.
* @param {string} metric Metric name.
* @param {string} as_of Optional date cell or "YYYY-MM-DD".
* @return The period.
* @customfunction
*/
function TA_PERIOD(ticker, metric, as_of) {
if (!ticker || !metric) return '#N/A ticker and metric required';
return taOld_(function () {
var cutOff = taAsOf_(as_of);
var f = taCompany_(PitCore.latestUrl(PitCore.ticker(ticker), cutOff), cutOff).data[String(metric).toLowerCase()];
return f ? PitCore.factPeriod(f) : '#N/A no ' + metric;
});
}
/**
* The filing date behind TA's figure. Earlier name for PIT_FILED.
*
* @param {string} ticker Company ID.
* @param {string} metric Metric name.
* @param {string} as_of Optional date cell or "YYYY-MM-DD".
* @return The filing date.
* @customfunction
*/
function TA_FILED(ticker, metric, as_of) {
if (!ticker || !metric) return '#N/A ticker and metric required';
return taOld_(function () {
var cutOff = taAsOf_(as_of);
var f = taCompany_(PitCore.latestUrl(PitCore.ticker(ticker), cutOff), cutOff).data[String(metric).toLowerCase()];
return f ? f.filed : '#N/A no ' + metric;
});
}
/**
* Every figure of one company, labelled, with its notices. Earlier name for PIT_TABLE.
*
* @param {string} ticker Company ID.
* @param {string} as_of Optional date cell or "YYYY-MM-DD".
* @return A block of rows.
* @customfunction
*/
function TA_TABLE(ticker, as_of) {
if (!ticker) return [['#N/A ticker required']];
try {
return PIT_TABLE(ticker, as_of);
} catch (e) {
return [['#ERROR ' + e.message]];
}
}
/**
* One row per filing, annual and quarterly, oldest first, then the notices.
*
* @param {string} ticker Company ID.
* @param {string} metric Metric name.
* @param {string} as_of Optional date cell or "YYYY-MM-DD".
* @return A block of rows.
* @customfunction
*/
function TA_HISTORY(ticker, metric, as_of) {
if (!ticker || !metric) return [['#N/A ticker and metric required']];
try {
var cutOff = taAsOf_(as_of);
var out = taCompany_(PitCore.historyUrl(PitCore.ticker(ticker), cutOff), cutOff);
var m = String(metric).toLowerCase();
var data = (out.data || []).filter(function (r) { return r.m === m; });
if (!data.length) return [['#N/A no data']];
var rows = [['Fiscal year', 'Period', 'Period end', 'Filed', 'Value', 'Unit', 'Form']];
data
.slice()
.sort(function (a, b) { return String(a.end).localeCompare(String(b.end)); })
.forEach(function (r) { rows.push([r.fy, r.fp, r.end, r.filed, r.v, r.u, r.form || '']); });
rows.push(['']);
return PitCore.rect(rows.concat(PitCore.noticeRows(out)));
} catch (e) {
return [['#ERROR ' + e.message]];
}
}
/**
* The notices for a company's figures. Earlier name for PIT_SOURCES.
*
* @param {string} ticker Company ID.
* @return A block of rows.
* @customfunction
*/
function TA_SOURCE(ticker) {
if (!ticker) return [['#N/A ticker required']];
try {
return PIT_SOURCES(ticker);
} catch (e) {
return [['#ERROR ' + e.message]];
}
}
/**
* Find a company's ID. Earlier name for PIT_SEARCH.
*
* @param {string} query Name or number.
* @param {string} market Optional market code.
* @return A block of rows.
* @customfunction
*/
function TA_SEARCH(query, market) {
if (!query) return [['#N/A query required']];
try {
var url = PitCore.BASE + '/api/search' + PitCore.queryString({ q: String(query), market: market ? String(market).toUpperCase() : '' });
return PitCore.searchTable(taFetchJsonRaw_(url));
} catch (e) {
return [['#ERROR ' + e.message]];
}
}
/**
* Logic shared by the Google Sheets add-on and the Excel add-in: reading what a
* person typed into a formula, choosing the row that answers it, and keeping the
* source notices that must travel with the figures. It touches no host API, so
* it runs the same in Apps Script, in Excel's runtime and in Node (the tests).
*/
var PitCore = (function () {
'use strict';
var BASE = 'https://www.tradingagentapp.com';
var API_PATH = '/api/v1/fundamentals';
/** Which row wins for one company and period: the same order as the API's own
* latest view (lib/fundamentals/period-rules.ts BASIS_RANK). */
var BASIS_RANK = { consolidated: 2, reported: 1, solo: 0 };
/** The marking Danish Data must show on the page itself when shown to anyone
* outside the holder's Authorised Users (Source Conditions 3.2(c)). */
var DK_MARKING = 'Reklamebeskyttet – må ikke bruges til direkte markedsføring / Advertising-protected – not for direct marketing';
var SUFFIX_MARKET = { TW: 'TW', KS: 'KR', KQ: 'KR', JP: 'JP', FR: 'FR', BE: 'BE', DK: 'DK', EE: 'EE', SE: 'SE', SK: 'SK', LV: 'LV', AU: 'AU', NZC: 'NZC' };
/** Our own functions, as they appear in a formula on either host. */
var FORMULA_CALL = /(?:^|[^A-Za-z0-9_.])(?:TA\.)?(?:PIT|TA)(?:_[A-Z]+)?\(\s*(?:"([^"]+)"|(\$?[A-Za-z]{1,3}\$?[0-9]+))/g;
function fail(kind, message) {
var e = new Error(message);
e.kind = kind;
throw e;
}
function isDate(v) {
return Object.prototype.toString.call(v) === '[object Date]';
}
function isBlank(v) {
return v === undefined || v === null || v === '';
}
function ticker(value) {
if (Array.isArray(value)) fail('value', 'Use one company per cell: give ticker a single cell, not a range.');
var s = isBlank(value) ? '' : String(value).trim().toUpperCase();
if (!s) fail('value', 'ticker is required, for example "2330.TW" or "AAPL".');
if (!/^[A-Z0-9.\-]{1,24}$/.test(s)) fail('value', 'ticker must be an ID such as "2330.TW", "AAPL" or "387949530.FR" (got "' + s.slice(0, 30) + '").');
return s;
}
function metric(value) {
if (Array.isArray(value)) fail('value', 'Use one metric per cell: give metric a single cell, not a range.');
var s = isBlank(value) ? '' : String(value).trim().toLowerCase();
if (!s) fail('value', 'metric is required, for example "revenue", "net_income" or "assets".');
if (!/^[a-z0-9_]{1,64}$/.test(s)) fail('value', 'metric must be a name such as "revenue" or "net_income" (got "' + s.slice(0, 30) + '").');
return s;
}
/**
* A year or quarter: 2024, "2024", "FY2024", "2024Q3", "2024 Q3", "Q3 2024".
* Returns { fy, fp } or null when blank. fp is "FY" for a year.
*/
function period(value) {
if (isBlank(value)) return null;
if (isDate(value)) fail('value', 'The third argument is a fiscal year such as 2024, not a date. A cut-off date goes in as_of, the fourth argument.');
var s = String(value).trim().toUpperCase().replace(/\s+/g, ' ');
if (!s) return null;
var m = /^(?:FY ?)?(\d{4})$/.exec(s);
if (m) return checkYear({ fy: Number(m[1]), fp: 'FY' });
m = /^(\d{4}) ?(Q[1-4])$/.exec(s) || /^(Q[1-4]) ?(\d{4})$/.exec(s);
if (m) {
return /^Q/.test(m[1])
? checkYear({ fy: Number(m[2]), fp: m[1] })
: checkYear({ fy: Number(m[1]), fp: m[2] });
}
return fail('value', 'The third argument is a fiscal year such as 2024, or a quarter such as "2024Q3" (got "' + s.slice(0, 30) + '").');
}
function checkYear(p) {
if (p.fy < 1990 || p.fy > 2100) fail('value', 'The year must be between 1990 and 2100 (got ' + p.fy + ').');
return p;
}
function periodLabel(p) {
return p.fp === 'FY' ? 'FY' + p.fy : p.fy + ' ' + p.fp;
}
function isoDay(s) {
var m = /^(\d{4})-(\d{2})-(\d{2})$/.exec(s);
if (!m) return false;
var d = new Date(Date.UTC(Number(m[1]), Number(m[2]) - 1, Number(m[3])));
return d.getUTCFullYear() === Number(m[1]) && d.getUTCMonth() === Number(m[2]) - 1 && d.getUTCDate() === Number(m[3]);
}
/**
* as_of, as YYYY-MM-DD; '' when blank. A date cell or a "YYYY-MM-DD" text.
* opts.formatDate turns a host Date into the day it shows (Sheets).
* opts.serial reads a number as an Excel date serial: Excel passes a date cell
* as a number, in no time zone, so no zone can move the day.
*/
function asOf(value, opts) {
opts = opts || {};
if (isBlank(value)) return '';
if (isDate(value) && opts.formatDate) return opts.formatDate(value);
if (typeof value === 'number' && opts.serial && value >= 1 && value < 2958466) {
var ms = Date.UTC(1899, 11, 30) + Math.floor(value) * 86400000;
return new Date(ms).toISOString().slice(0, 10);
}
var s = String(value).trim();
if (isoDay(s)) return s;
return fail('value', 'as_of must be a date cell or a "YYYY-MM-DD" text, for example "2022-01-01".');
}
function basis(value) {
var s = isBlank(value) ? '' : String(value).trim().toLowerCase();
if (!s) return '';
if (BASIS_RANK[s] === undefined) fail('value', 'basis must be "consolidated", "solo" or "reported" (got "' + s.slice(0, 30) + '").');
return s;
}
/** How many years to show; 0 means all of them. */
function yearCount(value) {
if (isBlank(value)) return 0;
var n = Number(value);
if (!(n >= 1) || n > 100 || Math.floor(n) !== n) fail('value', 'years must be a whole number from 1 to 100, or left blank for every year.');
return n;
}
function queryString(params) {
var parts = [];
Object.keys(params).forEach(function (k) {
if (!isBlank(params[k])) parts.push(encodeURIComponent(k) + '=' + encodeURIComponent(params[k]));
});
return parts.length ? '?' + parts.join('&') : '';
}
function url(params) {
return BASE + API_PATH + queryString(params);
}
function latestUrl(t, cutOff) {
return url({ ticker: t, view: 'latest', as_of: cutOff });
}
function historyUrl(t, cutOff) {
return url({ ticker: t, as_of: cutOff });
}
/** The API's own words for a refusal. Nothing is added to them. */
function errorMessage(status, body) {
if (body && typeof body.message === 'string' && body.message) return body.message;
if (body && typeof body.reason === 'string' && body.reason) return body.reason;
return 'The API answered HTTP ' + status + '.';
}
/** Only what the formulas read, so a reply fits the host's cache. */
function trim(out) {
var data = out.data;
return {
object: out.object,
ticker: out.ticker,
as_of: out.as_of,
full_history: out.full_history,
history_depth: out.history_depth ? { note: out.history_depth.note } : undefined,
attribution: out.attribution,
licence: out.licence,
data: Array.isArray(data)
? data.map(function (r) {
return { m: r.m, fy: r.fy, fp: r.fp, end: r.end, filed: r.filed, form: r.form, basis: r.basis, v: r.v, u: r.u, mkt: r.mkt };
})
: data
};
}
/** A history row in the shape of a latest-view fact. */
function fact(r) {
return { value: r.v, fy: r.fy, fp: r.fp, end: r.end, filed: r.filed, form: r.form, unit: r.u, basis: r.basis };
}
function better(a, b) {
var byBasis = (BASIS_RANK[a.basis] || 0) - (BASIS_RANK[b.basis] || 0);
if (byBasis !== 0) return byBasis > 0;
return String(a.filed) > String(b.filed);
}
function missing(out, what, cutOff) {
if (!hasData(out)) fail('none', 'No figures for ' + out.ticker + (cutOff ? ' in filings dated on or before ' + cutOff : '') + '. Check the ID: find it with PIT_SEARCH or on https://www.tradingagentapp.com/fundamentals/coverage.');
if (out.full_history === false && out.history_depth && out.history_depth.note) fail('depth', out.history_depth.note);
fail('none', 'No ' + what + ' for ' + out.ticker + (cutOff ? ' in filings dated on or before ' + cutOff : '') + '. A figure a company did not report is left blank, never estimated.');
}
/**
* The row for one metric in one year or quarter, from a history reply.
* A free key's reply about an older year holds no such row: the API's own
* history_depth note is the answer, not a figure from another year.
*/
function periodFact(out, m, p, b) {
var best = null;
(out.data || []).forEach(function (r) {
if (r.m !== m || r.fy !== p.fy || r.fp !== p.fp) return;
if (b && r.basis !== b) return;
if (!best || better(r, best)) best = r;
});
if (!best) missing(out, m + (b ? ' (' + b + ')' : '') + ' in ' + periodLabel(p), out.as_of_given);
return fact(best);
}
/** The latest figure on one basis, from a history reply. */
function latestOnBasis(out, m, b) {
var best = null;
(out.data || []).forEach(function (r) {
if (r.m !== m || r.basis !== b) return;
if (!best || r.end > best.end || (r.end === best.end && r.fp === 'FY' && best.fp !== 'FY')) best = r;
});
if (!best) missing(out, m + ' (' + b + ')', out.as_of_given);
return fact(best);
}
/** The figure from a latest-view reply. */
function latestFact(out, m) {
var f = out.data && out.data[m];
if (!f) missing(out, m, out.as_of_given);
return f;
}
function factPeriod(f) {
return f.fp && f.fp !== 'FY' ? f.fy + ' ' + f.fp : 'FY' + f.fy;
}
function marketsOf(out) {
return Object.keys((out && out.attribution) || {}).sort();
}
/** The rows every spilled block ends with: the way to its notices, and on the
* page itself the Danish marking, which may not sit behind a link. */
function footerRows(out) {
var rows = [['Sources: see the "Sources" tab, which this add-on keeps. Each figure\'s notice must stay with it.']];
if (marketsOf(out).indexOf('DK') !== -1) rows.push([DK_MARKING]);
return rows;
}
function rect(rows) {
var width = 0;
rows.forEach(function (r) { if (r.length > width) width = r.length; });
return rows.map(function (r) {
var o = r.slice();
while (o.length < width) o.push('');
return o;
});
}
/**
* One row per fiscal year, oldest first: what a history function spills. On a
* free key about a non-sample company this is one year, and the API's note
* says why, so one row can never read as the company's whole record.
*/
function historyTable(out, m, count, b) {
var byYear = {};
(out.data || []).forEach(function (r) {
if (r.m !== m || r.fp !== 'FY') return;
if (b && r.basis !== b) return;
if (!byYear[r.fy] || better(r, byYear[r.fy])) byYear[r.fy] = r;
});
var years = Object.keys(byYear).map(Number).sort(function (x, y) { return x - y; });
if (!years.length) missing(out, 'annual ' + m + (b ? ' (' + b + ')' : ''), out.as_of_given);
if (count) years = years.slice(-count);
var rows = [['Fiscal year', 'Value', 'Unit', 'Period end', 'Filed', 'Form', 'Basis']];
years.forEach(function (y) {
var r = byYear[y];
rows.push([r.fy, r.v, r.u, r.end, r.filed, r.form || '', r.basis || '']);
});
if (out.full_history === false && out.history_depth && out.history_depth.note) rows.push([out.history_depth.note]);
return rect(rows.concat(footerRows(out)));
}
/** Every figure of one company at its latest, with the notices in full. */
function latestTable(out) {
var data = out.data || {};
var keys = Object.keys(data).sort();
if (!keys.length) missing(out, 'figures', out.as_of_given);
var rows = [['Metric', 'Value', 'Unit', 'Fiscal year', 'Period', 'Period end', 'Filed', 'Form', 'Basis']];
keys.forEach(function (k) {
var f = data[k];
rows.push([k, f.value, f.unit, f.fy, factPeriod(f), f.end, f.filed, f.form || '', f.basis || '']);
});
rows.push(['']);
return rect(rows.concat(noticeRows(out)));
}
/** The notices of one reply as supplied: the market's own text, never edited. */
function noticeRows(out) {
var rows = [];
if (!hasData(out)) return rows;
marketsOf(out).forEach(function (m) { rows.push(['Source (' + m + ')', out.attribution[m]]); });
if (marketsOf(out).indexOf('DK') !== -1) rows.push(['Marking (DK)', DK_MARKING]);
if (out && out.licence) rows.push(['Licence', out.licence.name + ': ' + out.licence.url + '. ' + out.licence.notice]);
if (out && out.full_history === false && out.history_depth) rows.push(['Depth', out.history_depth.note]);
return rows;
}
function searchTable(out) {
var hits = (out && out.results) || [];
if (!hits.length) fail('none', 'No matches.');
var rows = [['Ticker', 'Name', 'Market']];
hits.forEach(function (h) { rows.push([h.ticker, h.name || '', h.market || '']); });
return rows;
}
/* ---------- The Sources tab ---------- */
function emptyStore() {
return { markets: {}, licences: {} };
}
function hasData(out) {
var d = out && out.data;
return Array.isArray(d) ? d.length > 0 : !!d && Object.keys(d).length > 0;
}
/** Add one reply's notices to the store. Returns the markets that changed.
* A reply with no figures adds nothing: an ID the API does not know answers
* with every market's notices, and none of them belongs to this file. */
function recordReply(store, out) {
var changed = [];
if (!hasData(out)) return changed;
marketsOf(out).forEach(function (m) {
if (out.attribution[m] && store.markets[m] !== out.attribution[m]) {
store.markets[m] = out.attribution[m];
changed.push(m);
}
});
if (out && out.licence && out.licence.name) {
var l = store.licences[out.licence.name];
if (!l || l.url !== out.licence.url || l.notice !== out.licence.notice) {
store.licences[out.licence.name] = { url: out.licence.url, notice: out.licence.notice };
changed.push('licence');
}
}
return changed;
}
function dataDate(notice) {
var m = /last updated (\d{4}-\d{2}-\d{2})/.exec(String(notice));
return m ? m[1] : '';
}
/** A cell's text can never run as a formula, whatever the source sends. */
function asText(s) {
s = String(s === undefined || s === null ? '' : s);
return /^[=+\-@]/.test(s) ? "'" + s : s;
}
var TAB_NAME = 'Sources';
var TAB_INTRO = 'Trading Agent Data keeps this tab: it lists the notice for every market whose figures were used in this file, and restores it if it is changed or removed. Keep it with the figures. If you copy figures elsewhere, take the notices with them.';
var TAB_HEADER = ['Market', 'Data date', 'Attribution Notice'];
var TAB_FOOT = [
'Compiled, processed and delivered by WU Capital Limited (NZBN 9429053731630), https://www.tradingagentapp.com',
'Withdrawn records are listed at ' + BASE + API_PATH + "/deletions. Delete them from your copy as clause 11 of the Data Terms requires. For Directly Licensed Data (New Zealand charities, Estonia, Australia) a listing is a notice only, and the licence named in that market's block decides what you must keep.",
'No Source Register has reviewed or endorsed this compilation.',
'Terms: ' + BASE + '/licences'
];
/** The whole tab, as rows of three columns. Empty when nothing was fetched. */
function tabRows(store) {
var markets = Object.keys(store.markets).sort();
if (!markets.length) return [];
var rows = [[TAB_NAME, '', ''], [TAB_INTRO, '', ''], TAB_HEADER.slice()];
markets.forEach(function (m) {
rows.push([m, dataDate(store.markets[m]), asText(store.markets[m])]);
if (m === 'DK') rows.push(['DK', '', DK_MARKING]);
});
rows.push(['', '', '']);
Object.keys(store.licences).sort().forEach(function (name) {
var l = store.licences[name];
rows.push(['Licence', '', asText(name + ': ' + l.url + '. ' + l.notice)]);
});
TAB_FOOT.forEach(function (line) { rows.push(['', '', line]); });
return rows;
}
/** Rebuild a store from the tab itself: the file carries its own record, so
* a market fetched in an earlier session is never dropped. */
function storeFromTab(values) {
var store = emptyStore();
(values || []).forEach(function (row) {
var a = String(row[0] || ''), c = String(row[2] || '');
if (!c) return;
if (/^[A-Z]{2,3}$/.test(a) && c !== DK_MARKING) store.markets[a] = c.replace(/^'/, '');
if (a === 'Licence') {
var m = /^(.*?): (https?:\/\/\S+?)\. (.*)$/.exec(c.replace(/^'/, ''));
if (m) store.licences[m[1]] = { url: m[2], notice: m[3] };
}
});
return store;
}
/** The list a sidebar or task pane shows. */
function sourcesList(store) {
return Object.keys(store.markets).sort().map(function (m) {
return { market: m, date: dataDate(store.markets[m]), notice: store.markets[m], marking: m === 'DK' ? DK_MARKING : '' };
});
}
function licencesList(store) {
return Object.keys(store.licences).sort().map(function (name) {
var l = store.licences[name];
return { name: name, url: l.url, notice: l.notice };
});
}
function marketOfTicker(t) {
var i = t.lastIndexOf('.');
if (i === -1) return 'US';
return SUFFIX_MARKET[t.slice(i + 1)] || '';
}
/** The first argument of each of our functions in a formula: tickers typed
* in it, and the cells it points to. */
function tickersInFormula(formula) {
var found = { literal: [], refs: [] };
if (!formula || String(formula).charAt(0) !== '=') return found;
var re = new RegExp(FORMULA_CALL.source, 'g');
var m;
while ((m = re.exec(String(formula)))) {
if (m[1]) found.literal.push(m[1].trim().toUpperCase());
else if (m[2]) found.refs.push(m[2].replace(/\$/g, '').toUpperCase());
}
return found;
}
/* ---------- Keys ---------- */
var KEY_RE = /^ta_(live|test)_[0-9a-f]{16,}$/;
function normaliseKey(value) {
var s = isBlank(value) ? '' : String(value).trim();
if (s === 'demo' || KEY_RE.test(s)) return s;
return fail('value', 'That does not look like a key. A key starts with ta_live_; the public demo key is the word demo.');
}
/** What may be shown of a key. The whole of it is never shown or logged. */
function maskKey(key) {
if (!key || key === 'demo') return 'demo (the public demo key)';
return key.slice(0, 8) + '…' + key.slice(-4);
}
return {
BASE: BASE,
API_PATH: API_PATH,
DK_MARKING: DK_MARKING,
TAB_NAME: TAB_NAME,
fail: fail,
ticker: ticker,
metric: metric,
period: period,
periodLabel: periodLabel,
asOf: asOf,
basis: basis,
yearCount: yearCount,
queryString: queryString,
latestUrl: latestUrl,
historyUrl: historyUrl,
errorMessage: errorMessage,
trim: trim,
periodFact: periodFact,
latestOnBasis: latestOnBasis,
latestFact: latestFact,
factPeriod: factPeriod,
marketsOf: marketsOf,
historyTable: historyTable,
latestTable: latestTable,
noticeRows: noticeRows,
searchTable: searchTable,
rect: rect,
emptyStore: emptyStore,
recordReply: recordReply,
dataDate: dataDate,
tabRows: tabRows,
storeFromTab: storeFromTab,
sourcesList: sourcesList,
licencesList: licencesList,
marketOfTicker: marketOfTicker,
tickersInFormula: tickersInFormula,
normaliseKey: normaliseKey,
maskKey: maskKey
};
})();
/** The sidebar, inline so this stays one file. Source: src/sidebar.html. */
var TA_SIDEBAR_HTML = "\n\n\n\n\n\n\n\n
Your key
\n
In use: …
\n \n \n \n
Stored in your own Google user properties, for you alone: anyone else opening this file uses their own key. Your keys are at account/api-keys.