Send data from Sierra Chart to Excel

Solution has two components

• SC Custom study
• Excel AddIn

Data is sent in real time

Main Features

• Time and Sales
• Market Depth
• Chart base data - OHLC, Volume, Trades, Bid Volume, Ask Volume, number of trades, etc.
• Chart subgraph data

Requirements

• Excel 365 - the AddIn makes use of some excel features that are currently only available in Excel 365
• Sierra Chart version 2299 or higher

Software Download

The solution includes two components:

1. A Sierra Chart custom study dll which you add to the chart
2. 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

• T29-SC-Cht-ExcelAddIn64-packed.xll
• T29-SC-Cht-ExcelAddIn-packed.xll

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 support@trade29.com and we'll handle it from there.

Quick Start

Lets start with the SC side

1. Register for the custom study
2. Start SierraChart and open a chart
3. Add the SC-Excel custom study to the chart
4. Set the key in the study settings. For the example below, set the key as "xx"

Next in Excel

1. Download the AddIn to your computer
2. Double click the AddIn xll file
3. Open a new blank workbook
4. 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)
• sc.cht.Debug()

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

=sc.cht.GetSGData2(key, data, include_timestamp)

• key
• 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

• Single - [SG3]

• Range - [SG3:SG5]

• List - [SG3, SG4, SG5]

• Mixed list and range [SG3:SG5, SG8, SG9]

rows - a single value or range of rows. the first row is the live bar and is numbered 0

• Single: [0] - just the live bar

• Single: [5] - just the 5th bar

• Range: [0:5] - rows 0-5, total of 6 rows

=sc.cht.GetSGData2("BTC1", "ID0.[SG1].[0:5]", TRUE)

=sc.cht.GetSGData2("BTC1", "ID1.[SG1:SG5].[0:5]", TRUE)

Videos

• Market depth - https://youtu.be/CWPuFFjJZsY
• Market depth with MBO - https://youtu.be/OuqYRwnoSC0

sc.cht.TimeAndSales

Filtering records

sc.cht.MarketDepth

SC Dom must be added to the chart with bid/ask columns

• Market depth - https://youtu.be/CWPuFFjJZsY

sc.cht.MarketDepth_MBO

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.

To configure:

1. 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
2. Still in SC, In the SC-Excel study settings, set Market Depth Recent Bid/Ask Enabled to "yes"
3. 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

FAQ's

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.