- Excel 365 - the AddIn makes use of some excel features that are currently only available in Excel 365
- Sierra Chart version 2299 or higher
The solution includes two components:
- A Sierra Chart custom study dll which you add to the chart
- An Excel AddIn file which is installed in Excel
Access to the Excel AddIn
The Excel AddIn comes as an XLL file. You will need one of these depending if you have Excel 32 or 64 bit
If you are not sure which one you have, in Excel, look under File->Account->About Excel
If you are still not sure, try one of them. If you get an error, try the other.
SC-Excel Custom Study Registration
Download and access to the custom study requires registration. We are working on automating this, in the meantime send an email to firstname.lastname@example.org and we'll handle it from there.
Lets start with the SC side
- Register for the custom study
- Start SierraChart and open a chart
- Add the SC-Excel custom study to the chart
- Set the key in the study settings. For the example below, set the key as "xx"
Next in Excel
- Download the AddIn to your computer
- Double click the AddIn xll file
- Open a new blank workbook
- In any cell, type =sc.cht.GetOHLC("xx", 0,5,TRUE)
The above command will post OHLC values for the most recent 5 bars on the chart with key "xx"
If all goes well, at this point you should see the 5 rows of data like in the image below.
Excel User Functions
- sc.cht.GetSGData(key, study_id, sg_number, bars_back, number_of_rows, include_bar_timestamp)
- sc.cht.GetSGData2(key, data, include_bar_timestamp)
- sc.cht.GetOHLC(key, bars_back, number_of_rows, include_bar_timestamp)
- sc.cht.GetOHLCV(key, bars_back, number_of_rows, include_bar_timestamp)
- sc.cht.TimeAndSales(key, number_of_rows)
- sc.cht.MarketDepth_MBO(key, number_of_rows)
- sc.cht.MarketDepth(key, number_of_rows, plot_mode)
The "key" Parameter
You will notice that the "key" parameter is the first parameter in each of the cht functions.
On the SC side, each chart you wish to access needs to have the "T29 SC-Excel" study added to it. In the study settings is a field called "Key". You can set this "key" to anything that makes sense. The only requirement is that it is unique and you don't use the same "key" on two different charts.
The "key" parameter ties the excel function with a specific chart.
Case does matter - if you set a key "XYZ" (upper case) in Sierra Chart, the key in Excel must also be "XYZ" ("xyz" lower case won't work).
=sc.cht.GetSGData2(key, data, include_timestamp)
- data - the study id, sg's and row to subscribe to. See format below)
- include_timestamp - whether to include a timestamp column nor not
The "data" parameter format
The second parameter to the function, "data" generally has the following format: study_id.[sg_number].[rows]
A simple example of this is something like sc.cht.GetSGData2("BTC1", "ID0.[SG1].[0:5]", TRUE)
In this example, data is set to ID0 (the main price graph) and retrieves the most recent 6 bars from SG1 (which happens to be the Open).
In more detail
study_id - formatted like it appears in SC. ID1, ID2, ID3 etc. ID0 is the main price graph.
sg_number - subgraphs are denoted similar to SC. SG1, SG2. SG3 etc. The field supports single values, range and list
rows - a single value or range of rows. the first row is the live bar and is numbered 0
Single:  - just the live bar
Single:  - just the 5th bar
Range: [0:5] - rows 0-5, total of 6 rows
Main price graph, Open, rows 0-5
=sc.cht.GetSGData2("BTC1", "ID0.[SG1].[0:5]", TRUE)
Main price graph, OHLC + Volume, rows 0-5
=sc.cht.GetSGData2("BTC1", "ID1.[SG1:SG5].[0:5]", TRUE)
Study with ID1, subgraphs 1-5, 7 and 8, rows 1-20
=sc.cht.GetSGData2("BTC1", "ID1.[SG1:SG5, SG7, SG8].[1:20]", TRUE)
SC Dom must be added to the chart with bid/ask columns
Sierra Chart Server Settings → Subscribe Market By Order Data When Market Depth Subscribe = Yes
Market depth - https://youtu.be/CWPuFFjJZsY
Market depth with MBO - https://youtu.be/OuqYRwnoSC0
Market Depth Recent Bid and Recent Ask
Sierra Chart supports Recent Bid and Recent Ask columns in the market depth - see here for more details - https://www.sierrachart.com/index.php?page=doc/GlobalTradeSettings.html#ColumnDescription_RecentBidVolume
See video -
This data is available the sc.cht.MarketDepth and sc.cht.MarketDepth_MBO excel functions.
- In SC, add the dom to the chart, add the recent bid and recent ask columns - you must have the recent bid and recent ask columns on the chart
- Still in SC, In the SC-Excel study settings, set Market Depth Recent Bid/Ask Enabled to "yes"
- In Excel, add the sc.cht.MarketDepth or sc.cht.MarketDepth_MBO to your sheet and set the params
If all goes well, you should see market depth with recent bid ask columns
Can I do anything to speed the refresh rate up?
Yes, settings can be changed on both sides Sierra Chart and Excel
Sierra Chart will periodically send data to Excel - how often this happens is controlled by the "Chart Update Interval".
As it receives data, Excel will periodically update the cells - how often this happens is controlled by the RTD throttle - see here for more Setting the RTD Throttle
What's the difference between GetSGData and GetSGData2? - which one should I use?
They both have the same purpose - receiving subgraph data from the chart. Originally we created GetSGData. After working with it a while we saw the need to add GetSGData2.
Using GetSGData you can grab data for a single subgraph. It has a very straight forward interface but it's limited to a single subgraph. If for example you had some study X that had 5 subgraphs in it and you wanted to grab all 5, you would need to add GetSGData 5 times to the spreadsheet which is fine but a bit tedious.
On the other hand GertSGData2 has a more complex syntax but it allows you to subscribe to those 5 subgraphs in one call.