SpreadServe Addin Worksheet FunctionsΒΆ
These are the functions you can invoke directly from cells in your spreadsheet.
s2about: get version information.
Parameters
- None
Return value: a string detailing the SpreadServe Addin version, and the version of Excel hosting the adding.
s2cron: setup scheduled timer.
Parameters
CronKey
: a value or cell reference evaluating to a string that matches a value in column C of the s2cfg sheet. The s2cfg row with the matching column C value will be used to specify a cron job. See the cron1, 2 or 3 example sheets.
Return value: “OK” if the function succeeds, an Excel error otherwise.
s2quandl: launch a quandl query.
Parameters
QueryKey
: a value or cell reference evaluating to a string that matches a value in column C of the s2cfg sheet. The s2cfg row with the matching column C value will be used to specify a quandl query. See the quandl1, 2 or 3 example sheets.Trigger
: an optional trigger. The value isn’t used inside the function, but a change in the input can be used to force repeat execution. See the quandl3.xls sheet for an example of an s2quandl trigger parameter hooked up to s2cron output to rerun a query on a timed basis.
Return value: “OK” if the function succeeds, an Excel error otherwise.
s2qcache: get a value from a quandl query result set. The position of the cell invoking this function is used to figure out which cell to get from the result set.
Parameters
QueryKey
: should match the QueryKey given to s2quandl.XOffset
: defaults to 0. If the left hand side of the result grid on your sheet is not column A this should be the number of columns across.YOffset
: defaults to 0. If the top row of the result grid on your sheet is not row 1 this should be the number of rows down.Trigger
: an optional trigger. The value isn’t used inside the function, but a change in the input can be used to force repeat execution. See the quandl3.xls sheet for an example of an s2quandl trigger parameter hooked up to s2cron output to rerun a query on a timed basis.
Return value: a value from the result set, or #N/A.
s2vqcache: a volatile version of s2qcache.
Parameters
QueryKey
: should match the QueryKey given to s2quandl.XOffset
: defaults to 0. If the left hand side of the result grid on your sheet is not column A this should be the number of columns across.YOffset
: defaults to 0. If the top row of the result grid on your sheet is not row 1 this should be the number of rows down.
Return value: a value from the result set, or #N/A.
s2tiingo: launch a tiingo query.
Parameters
QueryKey
: a value or cell reference evaluating to a string that matches a value in column C of the s2cfg sheet. The s2cfg row with the matching column C value will be used to specify a tiingo query. See the tiingo1 or 2 example sheets.Trigger
: an optional trigger. The value isn’t used inside the function, but a change in the input can be used to force repeat execution.
Return value: “OK” if the function succeeds, an Excel error otherwise.
s2tcache: get a value from a tiingo query result set. The position of the cell invoking this function is used to figure out which cell to get from the result set.
Parameters
QueryKey
: should match the QueryKey given to s2tiingo.XOffset
: defaults to 0. If the left hand side of the result grid on your sheet is not column A this should be the number of columns across.YOffset
: defaults to 0. If the top row of the result grid on your sheet is not row 1 this should be the number of rows down.Trigger
: an optional trigger. The value isn’t used inside the function, but a change in the input can be used to force repeat execution.
Return value: a value from the result set, or #N/A.
s2vtcache: a volatile version of s2tcache.
Parameters
QueryKey
: should match the QueryKey given to s2tiingo.XOffset
: defaults to 0. If the left hand side of the result grid on your sheet is not column A this should be the number of columns across.YOffset
: defaults to 0. If the top row of the result grid on your sheet is not row 1 this should be the number of rows down.
Return value: a value from the result set, or #N/A.
s2baremetrics: launch a Baremetrics metric query.
Parameters
QueryKey
: a value or cell reference evaluating to a string that matches a value in column C of the s2cfg sheet. The s2cfg row with the matching column C value will be used to specify a Baremetrics query. See the baremetrics_summary1 or baremetrics_metric1 example sheets.Trigger
: an optional trigger. The value isn’t used inside the function, but a change in the input can be used to force repeat execution.
Return value: “OK” if the function succeeds, an Excel error otherwise.
s2bcache: get a value from a Baremetrics query result set.
Parameters
QueryKey
: should match the QueryKey given to s2baremetrics.Date
: Baremetrics result sets are keyed on date; think of date as picking out a row. You should supply a string inyyyy-MM-dd
format, or use thes2today
function. Don’t use Excel’s volatileTODAY
function as you’ll cause an endless recalc cycle.Field
: pick out a column in the result set row selected byDate
.Trigger
: an optional trigger. The value isn’t used inside the function, but a change in the input can be used to force repeat execution.
Return value: a value from the result set, or #N/A.
s2vbcache: a volatile version of s2bcache.
Parameters
QueryKey
: should match the QueryKey given to s2baremetrics.Date
: Baremetrics result sets are keyed on date; think of date as picking out a row. You should supply a string inyyyy-MM-dd
format, or use the s2today function. Don’t use Excel’s volatileTODAY
function as you’ll cause an endless recalc cycle.Field
: pick out a column in the result set row selected byDate
.
Return value: a value from the result set, or #N/A.
s2sub: subscribe to RTD updates generated by s2cron, s2quandl or s2websock.
Parameters
SubCache
: [quandl|cron|websock]CacheKey
: should match the CronKey or QueryKey given to s2cron or s2quandl.Property
: [status|count|next|last|mX_Y_Z] count: cron event count for s2cron, rows in result set for s2quandl. next: time of next cron event. last: time of last cron event.
Return value: RTD value, or #N/A.
s2websock: subscribe via WebSockets to a page in a SpreadServe hosted sheet.
Parameters
SockKey
: a value or cell reference evaluating to a string that matches a value in column C of the s2cfg sheet. The s2cfg row with the matching column C value will be used to specify the URL of a page in a SpreadServe hosted spreadsheet. See the websock1 example sheet.
Return value: “OK” if the function succeeds, an Excel error otherwise.
s2twebsock: subscribe via WebSockets to a Tiingo market data feed.
Parameters
SockKey
: a value or cell reference evaluating to a string that matches a value in column C of the s2cfg sheet. The s2cfg row with the matching column C value will be used to specify the URL for the Tiingo websocket connection. See the tiingows1 example sheet.
Return value: “OK” if the function succeeds, an Excel error otherwise.
s2wscache: get a value from a WebSocket subscription cache.
Parameters
SockKey
: should match the SockKey given to s2websocket.CellKey
: for instance, m2_6_0 for col 3, row 7 on first sheet. Use ‘Page Source’ in your browser to examine the HTML on a page you want to subscribe to, and look for the div id tags to figure out the value you need.Trigger
: an optional trigger.
Return value: a value from the cache, or #N/A.
s2vwscache: a volatile version of s2wscache
.
Parameters
SockKey
: should match the SockKey given to s2websocket.CellKey
: for instance, m2_6_0 for col 3, row 7 on first sheet. Use ‘Page Source’ in your browser to examine the HTML on a page you want to subscribe to, and look for the div id tags to figure out the value you need.
Return value: a value from the cache, or #N/A.
s2today: non volatile alternative to Excel’s TODAY.
Parameters
Offset
: 0 to get today, -1 for yesterday, +1 for tomorrow, -7 for a week ago, +7 for a week from now.
Return value: a yyyy-MM-dd formatted date string.