Exporting historical data from ThinkorSwim for external analysis

[TOS2221]

Thank you very much for this!

 

[tommytx]

UPDATE: 1/22/2020, a newer version of this post is on thread #20.
https://usethinkscript.com/threads/...nkorswim-for-external-analysis.507/post-14606

I do a lot of back testing with PowerShell. I am learning Python, and will move to that in the future. 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 Open, High, Low, Close (OHLC) 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-OHLC.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.

https://tos.mx/TM4exi

View attachment 5212

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_EveryTickOHLC strategy

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;
def adjEndTime = endTime;

# we use a 1 bar offset to get orders to line up, so adjust for that here
def marketOpen = 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="OHLC|"+open[-1]+"|"+high[-1]+"|"+low[-1]+"|"+close[-1]);
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-OHLC.ps1, to transform the exported file.

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

[CmdletBinding()]
param (
    [string[]]
    $File,

    [switch]
    $ExportAsObject
)

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

$global:outData = [System.Collections.ArrayList]::new()

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 "\(OHLC"}

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

        $open  = $v[0] | Convert-CurrencyStringToDecimal
        $high  = $v[1] | Convert-CurrencyStringToDecimal
        $low   = $v[2] | Convert-CurrencyStringToDecimal
        $close = $v[3] | Convert-CurrencyStringToDecimal

        # add to our $outData array
        $null = $outData.Add(
            [PSCustomObject]@{
                'DateTime' = ([datetime]::Parse($item.'Date/Time'))
                'Open' = [decimal]$open
                'High' = [decimal]$high
                'Low' = [decimal]$low
                'Close' = [decimal]$close
                }
            )
    }
}

if ($ExportAsObject)
{
    # helpful message to show caller our output variable
    Write-Output "Out Data $($outData.Count) items (exported as `$outData)"
}
else
{
    # don't show any output, and just return the data to the pipeline
    return $outData
}

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

Examples:

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

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

The converted data is now an object:

D:\tos-data> $data[0..5] | ft

DateTime Open High Low Close
-------- ---- ---- --- -----
8/13/2019 5:20:00 AM 2874.75 2874.75 2873 2873.5
8/13/2019 5:21:00 AM 2873.25 2873.5 2872.75 2873.5
8/13/2019 5:22:00 AM 2873.25 2874 2873.25 2874
8/13/2019 5:23:00 AM 2874 2874 2872.5 2872.5
8/13/2019 5:24:00 AM 2872.75 2874 2872.75 2874
8/13/2019 5:25:00 AM 2874.25 2875 2873.75 2874.25

Save the $data object as a proper csv file:

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

View attachment 5214

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

@korygill that is the most fantastic idea i have ever seen. I have been struggling with what you did for months.. and i keep getting told thinkscript cannot do that but you made it do it.. My friend i will search for your handle and read ever line of text you have written here.. Hopefully i will see many ideas that with my little pea brain of thinkscript could never have thought of.. Besides i collect ever tiny thinkscript snippet that i can find as i am a horrible thinkscript programmer but i am fantastic at stitching others work together to sometimes come out with my own work of art.. Thank you so much for this.. It will improved my programming by a lot..
James your Fan.... for life.

 

[korygill]

Thanks for the kind words @tommytx and wish you much success in your TOS programming. Now that AI exists, much faster development and refactoring and learning is available.

 

[tommytx]

UPDATE: 1/22/2020, a newer version of this post is on thread #20.
https://usethinkscript.com/threads/...nkorswim-for-external-analysis.507/post-14606
I do a lot of back testing with PowerShell. I am learning Python, and will move to that in the future.

@korygill .. question.. you use the collected hloc data to back test.. what program do you place the data to make it run a back test. Like i use TOS a lot but as far as i know there is no way to convince it to use any external data for backtesting.. I would love to have the ability to back test based on my modified hloc data. For example i experiment with RSI a lot and have to go back and find a history of code that goes from say RSI 30 to RSI 70 in short order.. but usually takes hours or days.. I could modify the hloc to go from RSI 30 to RSI 70 in maybe 30 min for a qucker test but so far have not been able to achieve a way to feed my back test data to any engine other that Meta forex stuff that has text files available that you can see and access that they use for backtest.. and most like you cannot change those text files due to no edit capability not sure about that.. Does Trading View allow you to feed them with personal back test data.. Any suggestions would be great. If you could set up the data to make it do what you want for example gap up every 30 min etc. Then you could do some deep testing when working with how to best catch the gap. Would love to find a way to get hloc data into TOS.. And thanks again for showing me how to extract hloc from Tos.. I was told here on this site that what you did could not be done... That is what i like a man that sees a can't be done is just a challenge..Thanks again.

 

[rad14733]

@korygill .. question.. you use the collected hloc data to back test.. what program do you place the data to make it run a back test. Like i use TOS a lot but as far as i know there is no way to convince it to use any external data for backtesting.. I would love to have the ability to back test based on my modified hloc data. For example i experiment with RSI a lot and have to go back and find a history of code that goes from say RSI 30 to RSI 70 in short order.. but usually takes hours or days.. I could modify the hloc to go from RSI 30 to RSI 70 in maybe 30 min for a qucker test but so far have not been able to achieve a way to feed my back test data to any engine other that Meta forex stuff that has text files available that you can see and access that they use for backtest.. and most like you cannot change those text files due to no edit capability not sure about that.. Does Trading View allow you to feed them with personal back test data.. Any suggestions would be great. If you could set up the data to make it do what you want for example gap up every 30 min etc. Then you could do some deep testing when working with how to best catch the gap. Would love to find a way to get hloc data into TOS.. And thanks again for showing me how to extract hloc from Tos.. I was told here on this site that what you did could not be done... That is what i like a man that sees a can't be done is just a challenge..Thanks again.

@tommybc Unfortunately, in the TOS platform the only data we can import is Equity Symbols into Watchlists, nothing else... Several areas within the platform allow for exporting, but not importing...