Unlock the power of the Shooju Excel Add-In.
- Install
- Connect
- Reading from Shooju
- SJQuery
- SJTable
- SJRepDates
- SJFacet
- Writing to Shooju
- SJWrite
- Delete Point/Fields
- SJWriteReported
- SJDelete
- SJDeleteReported
- PowerQuery
- Additional Resources
- Frequently Asked Questions (FAQ)
Install
On the Shooju website, click to the Install Excel Add-In link under the wrench icon in the top-right:
Run the downloaded exe to install / uninstall / reinstall the Excell Add-In. Note that only Windows Excel versions 2007 and up are supported. You will be prompted to fully close Excel during the installation.
Connect
After opening Excel, click on the Login button in the Shooju ribbon:
Put in the url that you use for the Shooju website and press Login:
Reading from Shooju
SJQuery
To use a Queries to get fields and/or points for one or multiple Series :
=SJQuery("my query that results in one or more series", "*", "MAX", "-4w", -1)
This results in a collapsed array that looks like this:
To open it up use the Array Tools in the Shooju ribbon:
To specify a list of queries to fetch in one result with consolidated dates and fields, refer to the queries in the first parameter:
=SJQuery(A1:A10, "*", "MAX", "-4w", -1)
Note: If a range is used to select multiple cells (A1:A10, in the example above) each query must return only a single series. If a cell contains a query that returns >1 series, this must be the only query passed to SJQuery().
If you prefer to return points and field values cell-by-cell instead, use the following two functions:
=SJQueryPoint("my query that results in one series", DATE(2017,1,1))
=SJQueryField("my query that results in one series", "description")
For more information on how to input dates, see Date Input Notation . For exact dates, Shooju expects native Excel dates – either use a reference to a cell with a native Excel date or use the native DATE(YYYY,MM,DD) function.
SJTable
SJTable() is similar to SJQuery() but returns an Excel Table and requires param "Table Name"
=SJTable("my table name",<my query that results in one or more series>,"*","MIN","MAX")
SJRepDates
SJRepDates() returns the available point or field reported_dates for a specified series.
This function returns an array of dates if reported points/fields are available for the series between the specified dates.
Get Reported points:
=SJRepDates("<my query that results in a single series>, '2020-01-01', '2020-01-10', 'points')
Get Reported fields:
=SJRepDates(<my query that results in a single series>, '2020-01-01', '2020-01-10', 'fields')
SJFacet
SJFacet() returns all unique values of a field:
=SJFacet("field name", "query")
For more information, refer to Facets.
Writing to Shooju
SJWrite
SJWrite() writes points and/or fields to Shooju.
=SJWrite(<my query that results in a single series>, <range of fields>, <range of field values>, <range of dates>, <range of values>)
A few notes:
- Points and/or fields can be written in a single function call.
- Ranges must be continuous. For example, the
range of fields
can't be broken up into two ranges. - Ranges don't have to be next to each other, but they must be of the same length. For example the
range of fields
could be C7:C10 and therange of field values
could be D8:D11. This works because it's the same length. However, C7:C10 and D8:D10 wouldn't work.
Delete Point/Fields
To delete values for dates and/or fields, use Excel's built-in =NA() function for the new value for that field/point.
SJWriteReported
Same as SJWrite() but writes as reported date.
=SJWriteReported(<my query that results in a single series>, '2020-01-01', <range of fields>, <range of field values>, <range of dates>, <range of values>)
Note that both SJWrite() should be used in conjunction with SJWriteReported() if the points/fields being written are the 'latest' version.
SJDelete
This removes the series specified by the Series Query
=SJDelete(<my query that results in a single series>)
SJDeleteReported
This removes all points for a given Reported Date
=SJDeleteReported(<my query that results in a single series>, <Reported Date>)
PowerQuery
A unix timestamp is returned When using the Shooju API to retrieve points in PowerQuery. To convert this to an Excel datetime you can do something like:
#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [<API unix timestamp>]/1000)
Additional Resources
Refer to the Excel function wizard for detailed explanations of any functions or parameters.
Click on Insert Function when you have a Shooju function entered:
Or look for the Shooju category after clicking on Insert Function to see all Shooju functions:
Frequently Asked Questions (FAQ)
Getting #ERR: RTD topic not found?
Try the below:
- If the file is .XLS, save as .XLSX and reopen the file
- Reboot your machine.
- If neither of the above works, copy each sheet of the workbook into a new workbook by using
cntl-A
cntl-C
and into the new sheetcntl-V
.
Getting #ERR: RTD server not initialized or Shooju is gone from the Excel Ribbon?
This is most frequently due to the Shooju Add-in being disabled by Excel after a crash (Excel disabled add-ins after a crash to be on the safe side). To re-enable it:
- Click File, then Options on the very bottom, and then click Add-Ins
- On the bottom, next to Manage:, select Disabled Items and click Go
- Click Enable on any items that say Shooju
- If the above does not work, close Excel and reinstall the Addin using the steps at the top of this page.
Getting #ERR: the formula is too long or Unable to set the FormulaArray property of the Range class?
These errors come up when Excel can’t deal with the length of the formula. Excel formulas should not be over ~240 characters long.
If you are using the query inside of =SJQuery()
or =SJTable()
or you have a long fields list, simply put the query into another cell and reference it from within the formula, like =SJQuery(A1, ...)
to make the Excel formula shorter.
Getting “Microsoft Excel cannot access the file ‘C:\\…Shooju\2016\shooju2-x64.xll’. There are several possible reasons:”
The error may look something like this:
If the Add-in is not in that location, try simply re-installing. If the Add-in is in fact installed and is in that location, this is most frequently due to the Shooju Add-in being disabled by Excel after a crash (Excel disabled add-ins after a crash to be on the safe side). To re-enable it:
- Click File, then Options on the very bottom, and then click Add-Ins
- On the bottom, next to Manage:, select Disabled Items and click Go
- Click Enable on any items that say Shooju
Getting ComAddIn [Error] The Ribbon/COM add-in helper required by add-in Shooju could not be registered. This may be due to the helper add-in being disabled by Excel?
Try the below:
- Click the File tab.
- Click the Options button.
- Click the Trust Center option.
- Click the Trust Center Settings button.
- Click the Add-ins option. You will see three check boxes.
- The option “Require Application Add-ins to be signed by Trusted Publisher“ should be UNCHECKED.
The most common reason is Excel is in Manual Calculation mode. To check which calculation mode Excel is in, go to Formulas → Calculation Options:
Why is nothing loading? Everything is stuck in "Loading..." and the number of points left to load doesn't go down.
The most common reason is Excel is in Manual Calculation mode. To check which calculation mode Excel is in, go to Formulas → Calculation Options:
In Manual mode, Excel does not actively refresh Shooju formulas (as it shouldn't, since Manual mode tries to limit any automatic updating). To refresh, hit F9 (you may need to do so repeatedly until everything is loaded) or switch to Automatic mode.
How can I prevent Excel from refreshing my Shooju links when I open my workbook?
Occasionally, the Shooju Excel Add-in automatically updates values upon opening a workbook. This is typically caused by:
1. The workbook was last saved by another user or in a different version of Excel.
2. The workbook contains volatile inputs. For example, TODAY() or NOW().
How can I enable logging and send my logs to Shooju?
- Click About Shooju in the Excel Ribbon.
- Make sure Enable debug logging is checked.
- Go through the steps to replicate your issue/error message.
- Return to About Shooju and click View Log. Send the contents on this log to: support@shooju.com
How to initiate a refresh from VBA?
The Shooju Add-in exposes the following macros:
SJRefreshSelection - refreshes the current selection
SJRefreshSheet - refreshes the currently selected worksheet
SJRefreshWorkbook - refreshes the currently open workbook
SJRefreshAll - refreshes all the open workbooks
SJSubmitSelection - submits SJWrite*() for the current selection (beta only)
SJSubmitSheet - submits SJWrite*() for the currently selected worksheet (beta only)
SJSubmitWorkbook - submits SJWrite*() for the currently open workbook (beta only)
SJSubmitAll - submits SJWrite*() for all the open workbooks (beta only)
SJLoginForm - shows the login form
SJAboutForm - shows the addin’s about form
They can be used like any other macro in VBA like so:
Sub Test()
Application.Run "SJRefreshWorkbook"
End Sub
How to initiate a refresh without using the Ribbon or VBA?
Shooju exposes several Macros that can be called in VBA (see above). These Macros can also be initiated from the workbook without the use of VBA using:
SJCallMacro(<macro name>, Nonce) - executes one of the macro commands
- macro name is one of the above available macros
- Nonce is used to trigger re-calculation of the function when it’s entered in a cell; changing it to another value makes the function recalculate.
How to retrieve a single point from an expression supposed to return a scalar value?
As seen in Expressions, expressions that return a single scalar value actually return a series containing two points with the same value. This is the result when using =SJQuery()
.
However, it is possible to use =SJQueryPoint()
as a work-around to get a single point. The following example shows how to get a single point by modifying the expression ={{ sid=base\series }}.mean()
:
=SJQueryPoint("=sjs(r'sid=base\series', df='1990-01-01', dt='1991-01-01', max_points=-1).mean()","1990-01-01")
Notice that it is necessary to repeat the same date ("1990-01-01" in the example) both in df
and as an argument of the =SJQueryPoint()
function.
How to query an expression with globals?
Globals are a feature of Expressions to allow users to separate the expression into different parts.
The following expression:
# Expression
=G.series[G.series != 0]
# Globals
query = 'tsdbid=CI_RT_DLAP_SCE_APND_LMP'
G.series = sjs(query).resample('5Min').mean().isnull()
Can be queried in Excel like this (note how line breaks in globals are replaced with a ;
) :
=SJQuery("=G.series[G.series != 0]",,,,-1,,,,SJParams("g_expression", "query = 'tsdbid=CI_RT_DLAP_SCE_APND_LMP'; G.series = sjs(query).resample('5Min').mean().isnull()"))
What is my Excel Shooju query is taking too long?
Excel is not the best tool for HUGE requests of data. For these kind of requests, please look into using Python, or contact our support team.
Why am I seeing 0 as a result of SJQueryPoint(), SJQueryField() SJWrite(), or SJWriteReported()?
Excel sometimes displays 0 as an output of these functions. This usually indicates an internal Excel error. If a restart of Excel doesn’t help, please contact our support team.