Login Page - Create Account

Support Board


Date/Time: Wed, 26 Jan 2022 05:57:00 +0000



spreadsheet questions

View Count: 1766

[2013-05-14 22:48:57]
WarEagle - Posts: 58
I have a couple of questions about the spreadsheet that I could not find in the docs.

1) Can I access information about my orders and/or positions from within a sheet? For example, if I enter a limit order on a chart or DOM I would like to be able to track it in a spreadsheet. I thought perhaps with the auto trading capability that this information might be exposed to the sheet.

2) How often is the spreadsheet recalculated...by a new tick or each second?

3) Can the time that is returned for "LastTime" in the quote line in the spreadsheet be formatted so that it can be compared with the NOW() function? I tried this myself and even though both times are the same, I never get a return of true when comparing them (see pic).

https://www.sierrachart.com/Download.php?Folder=SupportBoard&download=436

Thanks!
imagetime_comparison.png / V - Attached On 2013-05-14 22:44:23 UTC - Size: 3.8 KB - 604 views
[2013-05-15 20:56:36]
Sierra Chart Engineering - Posts: 100044
1. You can access the current position if you are using the Spreadsheet System for Trading study.

2. This occurs when the chart is updated. For more information, refer to Chart Update Interval here:
http://www.sierrachart.com/index.php?l=doc/doc_ChartSettings.html

3. This is not going to work. NOW is going to include a date as well.
Sierra Chart Support - Engineering Level

Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy:
https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation

For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service:
Sierra Chart Teton Futures Order Routing
[2013-05-15 21:00:47]
tomgilb - Posts: 3134
You can strip the date from NOW() with this:

=NOW()-INT(NOW())

[2013-05-16 01:53:11]
WarEagle - Posts: 58
Thanks Tom for the tip. Unfortunately it still does not return true when that cell is the same as the LastTime value.

https://www.sierrachart.com/Download.php?Folder=SupportBoard&download=442

The problem I am trying to solve is that I would like to keep a running total of volume at price but reset when needed by clearing the spreadsheet. I used a formula to add the LastSize to the current value of a cell based on what price the trade was at. Since the sheet updates on bid/ask changes in the quote line I end up with the same LastSize value being added multiple times if no new trade has occurred since the last update. So my solution was to compare the LastTime of the print to the current time. If they match, add the new volume, if not, assume its the same print as the previous one. Is there another way to possibly do this?

Also I have a follow up question to point #1. If I am running the spreadsheet study on a chart would I be able to see the price of any limit orders that I have placed (not filled positions yet)? This question was because of a previous thread asking about the estimated position in the queue of a limit order. The response from Sierra was that they could not add this calculation based on the effect on performance of the software. My solution was to capture the price of my limit order in a spreadsheet, get the bid or offer size when the order shows up and then deduct any prints at that price while the order is live. Is this a possible workaround? I guess one other thing I would need access in the spreadsheet to would be the depth bid/ask data where my price is at.
imagetime_comparison.png / V - Attached On 2013-05-16 01:52:00 UTC - Size: 4.1 KB - 591 views
[2013-05-16 04:37:50]
tomgilb - Posts: 3134
Maybe you need to round the times in E1 and F1 to a 1 second multiple, like this:

=MROUND(NOW()-INT(NOW()),1/86400)

1/86400 = 1 second

Also, use the MROUND function on the formula in F1.

If I am running the spreadsheet study on a chart would I be able to see the price of any limit orders that I have placed (not filled positions yet)?
No, the spreadsheet study cannot do that.

Date Time Of Last Edit: 2013-05-16 04:38:04
[2013-05-16 12:51:11]
WarEagle - Posts: 58
Yay! That worked Tom. Thank you so much.


Edit: After testing my idea with this solution I see that I have reached the limitations of the spreadsheet and this will not be possible. It only gives me the last print of that second, so it misses every other print that occurs during the second. So if the tape shows 3, 50, 436, 1, 1, 2 print all in one second, all I get under LastSize is 2. So the cumulative volume can not be calculated that way. It was worth a try anyway and a good learning experience for me.
Date Time Of Last Edit: 2013-05-16 13:05:11

To post a message in this thread, you need to log in with your Sierra Chart account:

Login

Login Page - Create Account