Support Board
Date/Time: Mon, 25 Nov 2024 20:55:05 +0000
[User Discussion] - Working in SC Spreadsheet with datetime strings from Trade Activity Log
View Count: 309
| [2024-01-30 22:04:36] |
| j4ytr4der_ - Posts: 938 |
|
I was hoping to do some simple date comparisons in a SC Spreadsheet rather than having to leave SC and go to a Google Sheet just for this. Trouble is, dates in the TAL are provided as a text string in the format "2023-12-15 10:01:36.000", but since they're text values when copied out of the TAL, you cannot do any comparison operations on them. I thought maybe I could use LEFT() and RIGHT() to parse out the date and times separately, then use DATEVALUE() on the date portions to convert them to actual dates, but DATEVALUE() requires formatting of m/d/YYYY and won't work with the YYYY-m-d formatting that comes out of the TAL. Short of reformatting all the text strings (at which point this is just plain better to do in an external spreadsheet anyway), is there any way to get datetimes out of the TAL and do a comparison on them in a SC Spreadsheet? |
| [2024-01-31 00:58:52] |
| Sawtooth - Posts: 4120 |
|
You can do it using 5 Formula Columns: O3: 2023-12-15 10:01:36.000 P3: =VALUE(MID(O3,0,4)) Q3: =VALUE(MID(O3,5,2)) R3: =VALUE(MID(O3,8,2)) S3: =DATE(P3,Q3,R3) |
| [2024-01-31 01:56:23] |
| j4ytr4der_ - Posts: 938 |
|
Yeah that's gross lol... I suppose I could nest it all into a single cell though, maybe I'll try that. Thanks!
|
| [2024-01-31 01:59:11] |
| j4ytr4der_ - Posts: 938 |
|
Yep that'll get the job done. Ugly but functional. =DATE(VALUE(MID(F3,0,4)),VALUE(MID(F3,5,2)),VALUE(MID(F3,8,2))) |
| [2024-02-01 20:35:40] |
| ForgivingComputers.com - Posts: 960 |
|
Column A in a Sierra Spreadsheet Study is in Excel format. The Number underneath can be used instead of MDY-HMS. Put =A3 in K3 and you will see that "2024-02-01 14:15:00" is the same as 45323.59375. Easy to do comparisons. The whole number is days since the beginning of Excel time (1/1/1900) and the fraction is based on a 24-hour day. 24 * 0.59375 = 14.25 = 14:15:00
If you paste the Trade Activity Log into Excel, it will recognize dates and convert them internally. Create a custom Number Format to get milliseconds: m-d-yyyy h:mm:ss.000
Date Time Of Last Edit: 2024-02-01 20:47:26
|
 Excel Custom Date Format.png / V - Attached On 2024-02-01 20:45:43 UTC - Size: 34.79 KB - 52 views |
| [2024-02-01 23:51:43] |
| j4ytr4der_ - Posts: 938 |
|
Yeah I want to avoid using any external spreadsheet application for this, and I'm not sure how column A is relevant here? I need to copy data out of the TAL and paste it into an empty SC spreadsheet, which means all the dates turn into text strings, and then I want to do a comparison on those. Column A wouldn't be involved in any of that.
|
| [2024-02-03 14:09:02] |
| ForgivingComputers.com - Posts: 960 |
|
I was referring to column A from a spreadsheet study. Maybe you can get what you need from s_SCTradeOrder. Automated Trading From an Advanced Custom Study: s_SCTradeOrder Structure Members |
| [2024-02-03 15:16:05] |
| j4ytr4der_ - Posts: 938 |
|
Yes I understand you meant column A in a spreadsheet system. My point was that this number is the bar time. It doesn't have anything to do with what is copied out of the TAL, and since I'm pasting onto a completely empty sheet... I'm still unclear what the datetime column A has to do with this. And yes this could absolutely be done in ACSIL, but all I want to do is copy & paste onto a spreadsheet, not code up a study to act as a solution. I'm just trying to use SC as an alternative to Excel/Google Sheets in this one instance, since all I'm trying to do is very simply calculations that can easily be done in SC (provided the data is not text strings, which is the problem). |
To post a message in this thread, you need to log in with your Sierra Chart account: