Exporting historical data from ThinkorSwim for external analysis

a6z6a said:
I successfully used the strategy file in TOS from #20.

Instead of using Powershell to postprocess which is slow and results in UTF16 LE BOM encoded CSVs, you can use this Python code to go through each TOS exported CSV and concatenate them all into one giant CSV that you should import into your favorite SQL database like Postgres. I also hate Powershell :)

Python: 
import re
import glob
import os


rgx = r"(?:\(SOHLCP)\|(.*)?\|(.*)?\|(.*)?\|(.*)?\|(.*)?\|(.*)?\)(?:\;.*?\;.*?\;.*\;)(.*)?\;\;"
with open("tos_scraped.csv", "w") as fd_w:
    print("symbol,datetime,open,high,low,close,prevclose", file=fd_w)
    for p in glob.glob("StrategyReports*.csv"):
        with open(p) as fd_r:
            for line in fd_r:
                res = re.search(rgx, line)
                if res is None:
                    continue
                symbol, _open, high, low, close, prevclose, datetime = res.groups()
                print(','.join((
                    symbol.replace(",", ""),
                    datetime.replace(",", ""),
                    _open.replace(",", ""),
                    high.replace(",", ""),
                    low.replace(",", ""),
                    close.replace(",", ""),
                    prevclose.replace(",", ""))), file=fd_w)
Click to expand...
Is there a way to modify this code to pull historical options chain data for historical and expired contracts?
 
korygill said:
UPDATED workflow as of 1/22/2020

I do a lot of back testing with PowerShell. This article demonstrates how to export historical data from thinkorswim to a csv file, and then convert that output file to something that is a truly valid csv file which can be imported by PowerShell or any other program.

Overview of export process

This requires a few steps, and once you have done this once, subsequent exports will come naturally.

() Add a strategy to a chart that simulates a trade on every bar (1 buy to open, 1 sell to close).

This strategy will put Symbol, Open, High, Low, Close, PreviousClose (SOHLCP) data in the Name of the buy order. We will ignore the sell orders.

() Use the Show Report context menu to save the trades to a csv file.

The output file has extra information in it and it not a truly valid csv file.

() Run a PowerShell script I created called Get-SOHLCP.ps1 which transforms the csv file into a proper object which can be saved, or used in further analysis.

Export the strategy report

Open this chart in thinkorswim, it has the strategy code in it.

From your main TOS window, use Setup | Open Shared Item and paste this link in:
https://tos.mx/1FmyWu4
hmxvHwp.png


Every bar will have a buy and close. Right click one of the signals and export from Show Report menu item.

The strategy takes parameters for start/end times in the EST time zone.

Code for the kg_EveryTickSOHLCP strategy

Code: 
#
# kg_EveryTickSOHLCP
#
# Strategy to capture every bar OHLC and P, the previous close.
# Useful for exporting data from TOS into a CSV file for further processing.
#
# Author: Kory Gill, @korygill
#
declare upper;
declare once_per_bar;

input startTime = 820; #hint startTime: start time in EST 24-hour time
input endTime = 1600; #hint endTime: end time in EST 24-hour time

def adjStartTime = startTime;# - 1;
def adjEndTime = endTime;# - 1;

def agg = GetAggregationPeriod();

# we use a 1 bar offset to get orders to line up, so adjust for that here
def marketOpen = if agg >= AggregationPeriod.DAY then 1 else if SecondsTillTime(adjEndTime) >= 60 and SecondsFromTime(adjStartTime) >= -60 then 1 else 0;

AddOrder(OrderType.BUY_TO_OPEN,
    marketOpen,
    low,
    1,
    Color.White,
    Color.White,
    name="SOHLCP|"+GetSymbol()+"|"+open[-1]+"|"+high[-1]+"|"+low[-1]+"|"+close[-1]+"|"+close);
AddOrder(OrderType.SELL_TO_CLOSE, marketOpen, high, 1, Color.White, Color.White, name="SellClose");

Transform the thinkorswim csv file to data you can work with

Use this PowerShell script, Get-SOHLCP.ps1, to transform the exported file.

Save the code below as “Get-SOHLCP.ps1” and run similar to the steps shown below.

Code: 
#
# Get-SOHLCP.ps1
#
# Script to convert a TOS ThinkOrSwim kg_EveryTickOHLCP strategy report csv file to an object and/or proper csv data file.
#
# Author: Kory Gill, @korygill
#
[CmdletBinding()]
param (
    [string[]]
    $File,
    [switch]
    $ExportAsObject
)
function Convert-CurrencyStringToDecimal ([string]$input)
{
    ((($input -replace '\$') -replace '[)]') -replace '\(', '-') -replace '[^-0-9.]'
}
$global:sohlcpData = New-Object System.Collections.Generic.List[PSCustomObject]
foreach ($f in $File)
{
    if (-not (Test-Path $f))
    {
        throw "Cannot open file '$f'."
    }
    # read csv file
    $content = Get-Content -Path $f
    # find the lines that contain price information
    $csvdata = $content | ? {$_ -match ";.*;"} | ConvertFrom-Csv -Delimiter ';'
    # filter just the lines with (OHLC on them and make into CSV structure
    $data = $csvData | ? {$_ -match "\(SOHLCP"}
    foreach ($item in $data)
    {
        # capture the OHLC data
        $null = $item.Strategy -match "\(SOHLCP\|(.*)\)"
        $v = $Matches[1] -split '\|'
        $symbol = $v[0]
        $open  = $v[1] | Convert-CurrencyStringToDecimal
        $high  = $v[2] | Convert-CurrencyStringToDecimal
        $low   = $v[3] | Convert-CurrencyStringToDecimal
        $close = $v[4] | Convert-CurrencyStringToDecimal
        $prevClose = $v[5] | Convert-CurrencyStringToDecimal
        # add to our $sohlcpData array
        $null = $sohlcpData.Add(
            [PSCustomObject]@{
                'Symbol' = $symbol
                'DateTime' = ([datetime]::Parse($item.'Date/Time'))
                'Open' = [decimal]$open
                'High' = [decimal]$high
                'Low' = [decimal]$low
                'Close' = [decimal]$close
                'PrevClose' = [decimal]$prevClose
                }
            )
    }
}
if ($ExportAsObject)
{
    # helpful message to show caller our output variable
    Write-Output "Out Data $($sohlcpData.Count) items (exported as `$sohlcpData)"
}
else
{
    # don't show any output, and just return the data to the pipeline
    return $sohlcpData
}

From a PowerShell command window, run the script and pass your exported csv file as a parameter.

Examples:

$data = .\Get-OHLC.ps1 .\StrategyReports_ESXCME_81919.csv

$data = D:\tos-data\Get-OHLC.ps1 D:\tos-data\StrategyReports_ESXCME_81919.csv

The converted data is now an object:

$data | ft

Code: 
Symbol   DateTime                 Open    High     Low   Close PrevClose
------   --------                 ----    ----     ---   ----- ---------
/ES:XCME 1/17/2020 12:00:00 AM 3316.75 3330.25    3316 3323.75   3317.25
/ES:XCME 1/21/2020 12:00:00 AM    3325 3329.75 3307.25 3320.25   3323.75
/ES:XCME 1/22/2020 12:00:00 AM 3321.25  3337.5 3315.25 3317.25   3320.25

or

$data | ft

Code: 
Symbol   DateTime                Open    High     Low   Close PrevClose
------   --------                ----    ----     ---   ----- ---------
/ES:XCME 1/17/2020 6:30:00 AM 3325.25    3326 3323.75  3324.5    3325.5
/ES:XCME 1/17/2020 6:31:00 AM 3324.25  3324.5 3322.25 3322.25    3324.5
/ES:XCME 1/17/2020 6:32:00 AM 3322.25 3322.75 3320.75 3321.75   3322.25
/ES:XCME 1/17/2020 6:33:00 AM 3321.75  3322.5    3321 3322.25   3321.75
/ES:XCME 1/17/2020 6:34:00 AM 3322.25  3322.5  3321.5    3322   3322.25
/ES:XCME 1/17/2020 6:35:00 AM 3322.25  3323.5 3321.75    3323      3322
/ES:XCME 1/20/2020 6:30:00 AM    3321 3321.25  3320.5  3320.5   3320.75
/ES:XCME 1/20/2020 6:31:00 AM 3320.75 3320.75 3320.25  3320.5    3320.5
/ES:XCME 1/20/2020 6:32:00 AM  3320.5 3321.75  3320.5 3321.75    3320.5
/ES:XCME 1/20/2020 6:33:00 AM  3321.5 3321.75    3321 3321.25   3321.75
/ES:XCME 1/20/2020 6:34:00 AM 3321.25 3321.75    3321  3321.5   3321.25
/ES:XCME 1/20/2020 6:35:00 AM  3321.5 3321.75 3321.25 3321.75    3321.5

Save the $data object as a proper csv file:

$data | ConvertTo-Csv -NoTypeInformation | Out-File -FilePath d:\tos-data\ES-Data.csv

MjaURgY.png


Start back testing!

Now that you have data, you can back test your strategies with code outside thinkorswim. Note, you can only export up to 30 days of 1-minute data. You can use OnDemand to load data beyond that, but for this amount of work, maybe getting data from a provider is a better route. Kibot, http://www.kibot.com/, is a good source, and you can find others on the web. Lastly, if you only export the subset of data you need, you can save a lot of time running the strategy in thinkorswim.

Happy trading,
Kory Gill, @korygill
Click to expand...

Is there a way to make this an exe?

$data = D:\tos-data\Get-OHLC.ps1 D:\tos-data\StrategyReports_ESXCME_81919.csv
$data | ft
$data | ConvertTo-Csv -NoTypeInformation | Out-File -FilePath d:\tos-data\ES-Data.csv
 
luxurious08 said:
Is there a way to make this an exe?

$data = D:\tos-data\Get-OHLC.ps1 D:\tos-data\StrategyReports_ESXCME_81919.csv
$data | ft
$data | ConvertTo-Csv -NoTypeInformation | Out-File -FilePath d:\tos-data\ES-Data.csv
Click to expand...
Why would you want to do this? You could take those commands and put them in another PowerShell script. It's unclear what advantage an exe would give you in this case, not to mention, you'd be hard-coding the input and output files. It is technically possible to make a PowerShell script into an exe (search for it).
 
Hi, I'm getting this error message when I run the script:

PS C:\tosdata> $data = .\Get-SOHLCP.ps1 .\StrategyReports_ESXCME_62122.csv
Exception calling "Parse" with "1" argument(s): "String was not recognized as a valid DateTime."
At C:\tosdata\Get-SOHLCP.ps1:44 char:9
  • $null = $sohlcpData.Add(
  • Code: 
    ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FormatException
 
Exception calling "Parse" with "1" argument(s): "String was not recognized as a valid DateTime."
At C:\tosdata\Get-SOHLCP.ps1:44 char:9
+         $null = $sohlcpData.Add(
+         ~~~~~~~~~~~~~~~~~~~~~
 
Leom said:
Hi, I'm getting this error message when I run the script:

PS C:\tosdata> $data = .\Get-SOHLCP.ps1 .\StrategyReports_ESXCME_62122.csv
Exception calling "Parse" with "1" argument(s): "String was not recognized as a valid DateTime."
At C:\tosdata\Get-SOHLCP.ps1:44 char:9
  • $null = $sohlcpData.Add(
  • Code: 
    ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FormatException

Exception calling "Parse" with "1" argument(s): "String was not recognized as a valid DateTime."
At C:\tosdata\Get-SOHLCP.ps1:44 char:9
+         $null = $sohlcpData.Add(
+         ~~~~~~~~~~~~~~~~~~~~~
Click to expand...
See this post:
https://usethinkscript.com/threads/...nkorswim-for-external-analysis.507/post-79282

My guess is your date format does not match what the script is expecting.
 
Hello all,
First of all I really would like to thank @korygill for sharing his script. Being mostly interested in Futures trading I made some little modifications to the script to account for the different opening hours of the Futures market which operates 23 hours per day with a break from 5pm to 6pm (EST time). It seems to be working OK but it would be good to get some different opinions. In particular, since I am not sure about the role of the 1 bar offset adjustment done in the original script "to get orders to line up", I cannot judge if its mechanical porting to Futures data done by the modified code makes any sense. Below are the modified lines (everything before and after those lines is unchanged):

Code: 
input startBreakTime = 1700; #hint startBreakTime: start break time in EST 24-hour time
input endBreakTime = 1800; #hint endBreakTime: end break time in EST 24-hour time

def adjStartBreakTime = startBreakTime;# - 1;
def adjEndBreakTime = endBreakTime;# - 1;

def agg = GetAggregationPeriod();

# we use a 1 bar offset to get orders to line up, so adjust for that here
def marketOpen = if agg >= AggregationPeriod.DAY then 1 else if SecondsTillTime(adjStartBreakTime) < 60 and SecondsFromTime(adjEndBreakTime) < -60 then 0 else 1;

Thanks in advance to anyone interested in engaging.
 
Hi everyone, I just found this script and it works fantastically! Thank you @korygill! However, i have never used powershell before and even after watching the youtube video, couldnt get it to work, so i wrote a script in R to do the parsing of the data. Its not the most efficient code, but it works. Simply start R, copy this code in and run it after changing the file names. Hopefully this can help anyone that has trouble with the powershell portion of the workflow.

Code: 
install.packages("tidyr") #do this once only, it will download a package that is required by this script
install.packages("lubridate") #do this once only, it will download a package that is required by this script

library(tidyr)
library(lubridate)

file = read.delim("C:/filename.csv",sep=";",header=FALSE) #replace C:/filename.csv with your exported file name and location from Thinkorswim

file <- file[-c(1,2,3),]
names(file) <- file[1,]
file <- file[-1,]
file <- file[!grepl("Sell to Close",file$Side),]
file <- file[,c('Strategy','Date/Time')]
names(file) <- c("Strategy","Date")
file$Strategy <- gsub("[shared_kg_EveryTickOHLC(OHLC]","",file$Strategy)
file = separate(data = file, col = Strategy, into = c("Space","Open","High","Low","Close"), sep = "\\|")
file <- file[,-1]
file$Close <- gsub(")","",file$Close)
file = separate(data = file, col = Date, into = c("Date","Time"), sep = ",")
file <- file[,c(5,6,1,2,3,4)]
file$Date <- strptime(as.character(file$Date), "%m/%d/%y")
file$Date <- format(file$Date, "%d/%m/%Y")
file$Time <- parse_date_time(file$Time, '%I:%M %p')
file$Time <- paste(format(as.POSIXct(file$Time), format = "%H:%M"))


write.csv(file,"C:/exportedfilename.csv") #replace C:/exportedfilename.csv with where you want to export your file

*Updated to include formatting of dates
*Updated to included formatting of time
 
Last edited:
Hi Kory, is there a way to modify your code such that I can get 24-hour pricing for the emini and other futures contracts? I have tried adjusting the input (time) parameters and I can get 23 bars per day (I am using one-hour bars) but not 24. For example if I have the time parameters set to 0 and 2300 I get 23 bars with the missing bar being the midnight bar. If I set the time parameters to 0 and 0 the script fails (i.e. no OHLC are returned to the chart). I have searched the thread and I don't see that this subject has been addressed.
 
jekotn said:
Hi Kory, is there a way to modify your code such that I can get 24-hour pricing for the emini and other futures contracts? I have tried adjusting the input (time) parameters and I can get 23 bars per day (I am using one-hour bars) but not 24. For example if I have the time parameters set to 0 and 2300 I get 23 bars with the missing bar being the midnight bar. If I set the time parameters to 0 and 0 the script fails (i.e. no OHLC are returned to the chart). I have searched the thread and I don't see that this subject has been addressed.
Click to expand...
Hi jekotn, i had the same problem. I simply extended the chart time period by an extra day. If u want 3 days of data, set the chart to 4 days. U will be able to get that 1st candle's data then.
 
5thdream said:
Hi jekotn, i had the same problem. I simply extended the chart time period by an extra day. If u want 3 days of data, set the chart to 4 days. U will be able to get that 1st candle's data then.
Click to expand...
Hi 5th, thanks a lot for your message. I'm not quite sure what you mean. I have Kory's chart set to 20 days of one-hour data and the time input parameters are 0 and 2300. But on each of the 20 days I am missing data on the midnight bar. Were you using 0 and 2300 when you were able to get a complete day's data?
 
jekotn said:
Hi 5th, thanks a lot for your message. I'm not quite sure what you mean. I have Kory's chart set to 20 days of one-hour data and the time input parameters are 0 and 2300. But on each of the 20 days I am missing data on the midnight bar. Were you using 0 and 2300 when you were able to get a complete day's data?
Click to expand...
I just checked my settings, I have it set from 0 to 2359. Try that, it should solve your problem.
 
a6z6a said:
I successfully used the strategy file in TOS from #20.

Instead of using Powershell to postprocess which is slow and results in UTF16 LE BOM encoded CSVs, you can use this Python code to go through each TOS exported CSV and concatenate them all into one giant CSV that you should import into your favorite SQL database like Postgres. I also hate Powershell :)

Python: 
import re
import glob
import os


rgx = r"(?:\(SOHLCP)\|(.*)?\|(.*)?\|(.*)?\|(.*)?\|(.*)?\|(.*)?\)(?:\;.*?\;.*?\;.*\;)(.*)?\;\;"
with open("tos_scraped.csv", "w") as fd_w:
    print("symbol,datetime,open,high,low,close,prevclose", file=fd_w)
    for p in glob.glob("StrategyReports*.csv"):
        with open(p) as fd_r:
            for line in fd_r:
                res = re.search(rgx, line)
                if res is None:
                    continue
                symbol, _open, high, low, close, prevclose, datetime = res.groups()
                print(','.join((
                    symbol.replace(",", ""),
                    datetime.replace(",", ""),
                    _open.replace(",", ""),
                    high.replace(",", ""),
                    low.replace(",", ""),
                    close.replace(",", ""),
                    prevclose.replace(",", ""))), file=fd_w)
Click to expand...
may i know how to add volume to this script？ I tried to add volume after prevclose and python returns the errorValueError: not enough values to unpack (expected 8, got 7)
 
Thank you, I was unable to get results with powershell. Here is a python script that cleans up the excel:

import pandas as pd
workbook = pd.read_excel('StrategyReports_TSLA_22123.xlsx')
sym = workbook.iloc[0,0].split(" ")[1]
numRows = len(workbook.index)
#print(numRows)
l1 = []
l2 = []
l3 = []
l4 = []
l5 = []
l6 = []
i = 6
while i < numRows-6:
stringPrice = workbook.iloc[i,0].split("|")
open = stringPrice[1]
high = stringPrice[2]
low = stringPrice[3]
close = stringPrice[4].split(")")[0]
date = stringPrice[4].split(";")[4]
stringTime = workbook.iloc[i,1]
time = stringTime.split(";")[0]
l1.append(date)
l2.append(time)
l3.append(open)
l4.append(high)
l5.append(low)
l6.append(close)
i += 2
stocks = pd.DataFrame(list(zip(l1, l2, l3, l4, l5, l6)))
stocks.columns =['Date', 'Time', 'Open', 'High', 'Low', 'Close']
fn_date1 = l1[0].split("/")
fn_date2 = l1[-1].split("/")
fn_prefix = fn_date1[0]+""+fn_date1[1]+""+fn_date1[2] +"thru"+fn_date2[0]+""+fn_date2[1]+""+fn_date2[2]
stocks.to_excel(sym+ '_' + fn_prefix + '_1min.xlsx', sheet_name=sym, header = True)
 
I wrote some JavaScript to convert it to the ninjatrader format. It seems to work but I probably won't use it :cry:

ThinkOrSwim:

Code: 
declare upper;
declare once_per_bar;

def time = GetTime();

AddOrder(OrderType.BUY_TO_OPEN, 1, 0, 1, Color.WHITE, Color.WHITE, name="TOHLCV|"+AsPrice(time)+"|"+open[-1]+"|"+high[-1]+"|"+low[-1]+"|"+close[-1]+"|"+AsPrice(volume[-1]));
AddOrder(OrderType.SELL_TO_CLOSE, 1, 0, 1, Color.WHITE, Color.WHITE);

Node.js Script "tos2nt.js":

JavaScript: 
const fs = require('fs').promises;

(async () => {
    const [file] = process.argv.slice(-1);
    const buffer = await fs.readFile(file);
    const lines = buffer.toString().split(/\r?\n/);
    let symbol = 'NO_SYMBOL';
    let output = '';
    let lastInfo = null;
    for (const line of lines) {
        const symbolMatch = /Symbol: (.+)/.exec(line);
        if (symbolMatch != null) {
            symbol = symbolMatch[1];
        }
        const match = /\(TOHLCV\|(.+)\|(.+)\|(.+)\|(.+)\|(.+)\|(.+)\)/.exec(line);
        if (match != null) {
            let date = new Date(parseInt(match[1]));
            let info = [parseFloat(match[2]), parseFloat(match[3]), parseFloat(match[4]), parseFloat(match[5]), parseFloat(match[6])];
            if (lastInfo != null) {
                output += date.toISOString().replace(/T/, ' ').replace(/[-:]|\.000Z/g, '');
                for (const i of lastInfo) {
                    output += ';' + i;
                }
                output += '\n';
            }
            lastInfo = info;
        }
    }
    await fs.writeFile(symbol + ".Last.txt", output);
})();

Batch file wrapper "tos2nt.bat" (Drag StrategyReports.csv onto this in windows explorer to convert quickly, one at a time though)

Code: 
node tos2nt.js %*

Example input:

Code: 
Strategy report
Symbol: GOVZ
Work Time: 7/5/22, 8:40 AM - 3/21/23, 2:55 PM


Id;Strategy;Side;Amount;Price;Date/Time;Trade P/L;P/L;Position;
1;AAAExportData;Sell to Close;-1.0;$16.05;7/5/22, 8:40 AM;$0.00;($0.01);-1.0;
2;AAAExportData(TOHLCV|1657028100000|16.05|16.06|16.05|16.06|944);Buy to Open;1.0;$16.05;7/5/22, 8:40 AM;;$0.00;0.0;
3;AAAExportData;Sell to Close;-1.0;$16.02;7/5/22, 8:45 AM;$0.00;$0.00;-1.0;
4;AAAExportData(TOHLCV|1657028400000|16.02|16.02|16.02|16.02|200);Buy to Open;1.0;$16.02;7/5/22, 8:45 AM;;$0.00;0.0;
5;AAAExportData;Sell to Close;-1.0;$16.01;7/5/22, 8:50 AM;$0.00;($0.03);-1.0;
6;AAAExportData(TOHLCV|1657028700000|16.01|16.04|16.01|16.04|400);Buy to Open;1.0;$16.01;7/5/22, 8:50 AM;;$0.00;0.0;
7;AAAExportData;Sell to Close;-1.0;$16.00;7/5/22, 8:55 AM;$0.00;$0.03;-1.0;
8;AAAExportData(TOHLCV|1657029000000|15.995|15.995|15.96|15.96|273);Buy to Open;1.0;$16.00;7/5/22, 8:55 AM;;$0.00;0.0;
9;AAAExportData;Sell to Close;-1.0;$15.96;7/5/22, 9:05 AM;$0.00;$0.00;-1.0;
10;AAAExportData(TOHLCV|1657029300000|15.9621|15.9621|15.9621|15.9621|350);Buy to Open;1.0;$15.96;7/5/22, 9:05 AM;;$0.00;0.0;
11;AAAExportData;Sell to Close;-1.0;$15.93;7/5/22, 9:10 AM;$0.00;$0.00;-1.0;
12;AAAExportData(TOHLCV|1657029900000|15.93|15.93|15.93|15.93|300);Buy to Open;1.0;$15.93;7/5/22, 9:10 AM;;$0.00;0.0;
13;AAAExportData;Sell to Close;-1.0;$16.00;7/5/22, 9:30 AM;$0.00;$0.00;-1.0;
14;AAAExportData(TOHLCV|1657030200000|16|16|15.9875|16|800);Buy to Open;1.0;$16.00;7/5/22, 9:30 AM;;$0.00;0.0;
15;AAAExportData;Sell to Close;-1.0;$15.92;7/5/22, 9:40 AM;$0.00;$0.00;-1.0;
...

Example output:

Code: 
20220705 134000;16.05;16.06;16.05;16.06;944
20220705 134500;16.02;16.02;16.02;16.02;200
20220705 135000;16.01;16.04;16.01;16.04;400
20220705 135500;15.995;15.995;15.96;15.96;273
20220705 140500;15.9621;15.9621;15.9621;15.9621;350
20220705 141000;15.93;15.93;15.93;15.93;300
20220705 143000;16;16;15.9875;16;800
...
 
The PowerShell post processing isn't necessary. With some mods, the strategy report can be directly imported into Excel.

For either of below, import result into Excel as a text file using delimeter ";". Retitle and delete columns to clean it up.

Simple case when wanting all bars:

Code: 
declare upper;
declare once_per_bar;

AddOrder( OrderType.BUY_TO_OPEN, BarNumber()%2, 0, 0, Color.White, Color.White, name="OHLC|;"+open[-1]+";"+high[-1]+";"+low[-1]+";"+close[-1]+";");
AddOrder( OrderType.SELL_TO_CLOSE, !(BarNumber()%2), 0, 0, Color.White, Color.White, name="OHLC|;"+open[-1]+";"+high[-1]+";"+low[-1]+";"+close[-1]+";");

General case, including when wanting only specified time range (also fixes bugs in prior approach):

Code: 
declare upper;
declare once_per_bar;

input startTime = 930; #start time in EST 24-hour time
input endTime = 1600; #end time in EST 24-hour time
# set both times to 0 to include every bar

def agg = GetAggregationPeriod();
def barsecs = agg / 1000;

# include every bar if >=Day chart or user directs, otherwise constrain bars to specified time window
def marketOpen =
(agg >= AggregationPeriod.DAY) or
(startTime == 0 and endTime == 0) or
(SecondsTillTime(endTime) > barsecs and SecondsFromTime(startTime) > -2 * barsecs);

# need to keep track whether a position is open or not.  If going to enter an order then toggle, otherwise leave alone
def positionOpen =
if BarNumber()==1 then if marketOpen then 1 else 0
else if marketOpen then !positionOpen[1] else positionOpen[1];

# tee up directions for order placement
def openOrder = if marketOpen then positionOpen else 0;
def closeOrder = if marketOpen then !positionOpen else 0;

AddOrder( OrderType.BUY_TO_OPEN, openOrder, 0, 0, Color.White, Color.White, name="OHLC|;"+open[-1]+";"+high[-1]+";"+low[-1]+";"+close[-1]+";");
AddOrder( OrderType.SELL_TO_CLOSE, closeOrder, 0, 0, Color.White, Color.White, name="OHLC|;"+open[-1]+";"+high[-1]+";"+low[-1]+";"+close[-1]+";");
 
Last edited:
korygill said:
Update on 3/5/2020

See my YouTube video with a walk through of how to export historical data and run a PowerShell script to generate a proper csv file. Also includes adding additional data like other indicators to the csv data.

See previous posts for more information as well about exporting historical data.
https://usethinkscript.com/threads/...nkorswim-for-external-analysis.507/post-14606

Video:
Exporting Data With thinkorswim by Kory Gill, @korygill

Strategy Code:

Code: 
#
# kg_EveryTickSOHLCP
#
# Strategy to capture every bar OHLC and P, the previous close.
# Useful for exporting data from TOS into a CSV file for further processing.
#
# Author: Kory Gill, @korygill
#
declare upper;
declare once_per_bar;

input startTime = 820; #hint startTime: start time in EST 24-hour time
input endTime = 1600; #hint endTime: end time in EST 24-hour time

def adjStartTime = startTime;# - 1;
def adjEndTime = endTime;# - 1;

def agg = GetAggregationPeriod();

# we use a 1 bar offset to get orders to line up, so adjust for that here
def marketOpen = if agg >= AggregationPeriod.DAY then 1 else if SecondsTillTime(adjEndTime) >= 60 and SecondsFromTime(adjStartTime) >= -60 then 1 else 0;

# get indicator values (note, these are optional and were a demonstration to show how you can do these things..
# comment them out if you just want SOHLCP data using the # symbol on front of line.
# if you do, change that name= line below to just (no leading # commend character of course):
# name="SOHLCP|"+GetSymbol()+"|"+open[-1]+"|"+high[-1]+"|"+low[-1]+"|"+close[-1]+"|"+close);
def macdValue = MACD().Value;
def macdAvg = MACD().Avg;


AddOrder(OrderType.BUY_TO_OPEN,
    marketOpen,
    low,
    1,
    Color.White,
    Color.White,
    name="SOHLCP|"+GetSymbol()+"|"+open[-1]+"|"+high[-1]+"|"+low[-1]+"|"+close[-1]+"|"+close+"|"+macdValue[-1]+"|"+macdAvg[-1]);
AddOrder(OrderType.SELL_TO_CLOSE, marketOpen, high, 1, Color.White, Color.White, name="SellClose");

PowerShell code for Get-SOHLCP.ps1:

Code: 
#
# Get-SOHLCP.ps1
#
# Script to convert a TOS ThinkOrSwim kg_EveryTickSOHLCP strategy report csv file to an object and/or proper csv data file.
#
# Author: Kory Gill, @korygill
# Version: 20200304.2300
#
# Examples
# $x = gci 'D:\Database\MarketData\TOS-StrategyReport-Files-SOHLCP\UseThinkScriptDemo\StrategyReport*' | Select -ExpandProperty FullName | D:\Source\Repos\technical-analysis-dev\dev\Get-SOHLCP.ps1 -InformationAction Continue
#
# Get the symbol back:
# $m = 'SOHLCP-(~ES.XCME)(1.21.20 5.00 AM - 1.27.20 1.14 PM).csv' | Select-String -Pattern '\((.*?)\)'  -AllMatches; $m.Matches[0].Groups[1].Value -replace '~', '/'
#
[CmdletBinding()]
param (
    [Parameter(Mandatory=$true, ValueFromPipeline=$true)]
    [string[]]
    $File,

    [switch]
    $ExportAsObject
)

Begin {
    function Convert-CurrencyStringToDecimal ([string]$input)
    {
        ((($input -replace '\$') -replace '[)]') -replace '\(', '-') -replace '[^-0-9.]'
    }

    $global:sohlcpAllData = New-Object System.Collections.Generic.List[PSCustomObject]
}

Process {
    foreach ($f in $File)
    {
        if (-not (Test-Path $f))
        {
            throw "Cannot open file '$f'."
        }

        Write-Information "Processing file: '$f'."

        # read csv file
        $content = Get-Content -Path $f

        # generate filename
        $csvSymbol = ($content[1] -split 'Symbol: ')[1] -replace '/', '~' -replace ':', '.'
        $csvWorkTime = ($content[2] -split 'Work Time: ')[1] -replace '/', '.' -replace ':', '.'
        $outFile = 'SOHLCP-(' + $csvSymbol +')('+ $csvWorkTime + ')'

        # find the lines that contain price information
        $csvdata = $content | ? {$_ -match ";.*;"} | ConvertFrom-Csv -Delimiter ';'

        # filter just the lines with (OHLC on them and make into CSV structure
        $data = $csvData | ? {$_ -match "\(SOHLCP"}

        $sohlcpFileData = New-Object System.Collections.Generic.List[PSCustomObject]

        foreach ($item in $data)
        {
            # capture the OHLC data
            $null = $item.Strategy -match "\(SOHLCP\|(.*)\)"
            $v = $Matches[1] -split '\|'

            $symbol = $v[0]
            $open  = $v[1] | Convert-CurrencyStringToDecimal
            $high  = $v[2] | Convert-CurrencyStringToDecimal
            $low   = $v[3] | Convert-CurrencyStringToDecimal
            $close = $v[4] | Convert-CurrencyStringToDecimal
            $prevClose = $v[5] | Convert-CurrencyStringToDecimal
            $macdValue = $v[6] | Convert-CurrencyStringToDecimal
            $macdAvg = $v[7] | Convert-CurrencyStringToDecimal

            $sohlcpData = [PSCustomObject]@{
                'Symbol' = $symbol
                'DateTime' = ([datetime]::Parse($item.'Date/Time'))
                'Open' = [decimal]$open
                'High' = [decimal]$high
                'Low' = [decimal]$low
                'Close' = [decimal]$close
                'PrevClose' = [decimal]$prevClose
                'macdValue' = [decimal]$macdValue
                'macdAvg' = [decimal]$macdAvg
                }

            # add to our $sohlcpData array
            $null = $sohlcpFileData.Add($sohlcpData)
            $null = $sohlcpAllData.Add($sohlcpData)
        }

        # save to file
        $sohlcpFileData | Export-Csv -Path (Join-Path (Split-Path -Path $f -Parent) ($outFile + '.csv')) -Force -NoTypeInformation -Encoding ASCII
    }
}

End {
    if ($ExportAsObject)
    {
        # helpful message to show caller our output variable
        Write-Information "Out Data $($sohlcpAllData.Count) items (exported as `$sohlcpAllData)"
    }
    else
    {
        # don't show any extraneous output, and just return the data to the pipeline
        return $sohlcpAllData
    }
}
Click to expand...
Hey Kory, thank you for providing all of this! I'm running into trouble when trying to export anything more than ohlc data using these versions of the scripts. I get a proper csv file that I can import into google sheets, but the additional data (I tried the macd values in your example, and I tried volume, which is what I really want) just...doesn't appear. It's still just the ohlc data. Any idea where I'm going wrong? I c/p your strategy with macd studies and the original .ps1 script and still get this result.
 
You must log in or register to reply here.

Join useThinkScript to post your question to a community of 21,000+ developers and traders.

SIGN UP NOW LEARN MORE
Thread starter Similar threads Forum Replies Date
A Exporting Historical data for the non-coder For ThinkOrSwim Tutorials 2
V Transferring Thinkorswim data to New account Tutorials 1
S Why You Shouldn't Try To Work With Options Data On Charts In ThinkOrSwim Tutorials 2
T Reading Options Data In ThinkOrSwim Tutorials 4
MerryDay What Data Is Available In Scans & Watchlists For ThinkOrSwim Tutorials 0

Similar threads

Not the exact question you're looking for?

Start a new thread and receive assistance from our community.

87k+ Posts
352 Online
Create Post

Similar threads

Similar threads

The Market Trading Game Changer

Join 2,500+ subscribers inside the useThinkScript VIP Membership Club
  • Exclusive indicators
  • Proven strategies & setups
  • Private Discord community
  • ‘Buy The Dip’ signal alerts
  • Exclusive members-only content
  • Add-ons and resources
  • 1 full year of unlimited support
Try VIP Club Today

Frequently Asked Questions

What is useThinkScript?

useThinkScript is the #1 community of stock market investors using indicators and other tools to power their trading strategies. Traders of all skill levels use our forums to learn about scripting and indicators, help each other, and discover new ways to gain an edge in the markets.

How do I get started?

We get it. Our forum can be intimidating, if not overwhelming. With thousands of topics, tens of thousands of posts, our community has created an incredibly deep knowledge base for stock traders. No one can ever exhaust every resource provided on our site.

If you are new, or just looking for guidance, here are some helpful links to get you started.

What are the benefits of VIP Membership?
VIP members get exclusive access to these proven and tested premium indicators: Buy the Dip, Advanced Market Moves 2.0, Take Profit, and Volatility Trading Range. In addition, VIP members get access to over 50 VIP-only custom indicators, add-ons, and strategies, private VIP-only forums, private Discord channel to discuss trades and strategies in real-time, customer support, trade alerts, and much more. Learn all about VIP membership here.
How can I access the premium indicators?
To access the premium indicators, which are plug and play ready, sign up for VIP membership here.
Back
Top