Exporting historical data from ThinkorSwim for external analysis

[korygill]

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

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

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

 

[BenTen]

Thank you for this!

 

[diazlaz]

Awesome idea and great work. Thanks for sharing.

 

[mwalstea]

This is great thank you...I was wondering, within thinkorswim, is there a way to have the current day close plotted in a column as the Open, High, and Low are. I'm tracking all of them but the I just realized thinkorswim only has PREVIOUS day close, thats useless to me, I need the PRESENT day close...Would appreciate any help, thank you!

 

[tomsk]

The watchlist has a column called "LAST" - that is the current close. If you want to have a custom watchlist column for the previous close, just reference close[1].

Note that the default watchlist already has a "Net Change" column so you can have an idea of what the chose is relative to the previous close.

 

[korygill]

This is great thank you...I was wondering, within thinkorswim, is there a way to have the current day close plotted in a column as the Open, High, and Low are. I'm tracking all of them but the I just realized thinkorswim only has PREVIOUS day close, thats useless to me, I need the PRESENT day close...Would appreciate any help, thank you!

You may need to elaborate more on what you are trying to do. The code I provided should export every "closed" bar on the chart. I used 1m charts. If you are using daily/1d charts, then the strategy will not fire until that bar is "closed", so that could make it not report the current day if exporting during market hours. This is just a quirk of how strategies work on TOS.

 

[qaw]

Does anyone have a template code for profit/loss for use in TOS charts? It will be very handy for doing backtests.

 

[qaw]

Does anyone have a template code for profit/loss for use in TOS charts? It will be very handy for doing backtests.

Mod moved my Thread as a reply here. Could I have a seperate thread for this request? Though related ,it is a different topic .

 

[dashsten]

@korygill any idea why I'm having issues with trying to use it for exporting daily data? all the lower time frames work. Just isn't working for daily

 

[korygill]

Yeah, that's an oversight in the original script.

Replace this line

# 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;

With these lines:

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;

 

[dashsten]

You....Are the man!! Thanks for the prompt response!

 

[narensiri]

This is cool.. i am trying to extract data from my chart with indicators.
Currentl, I get the values of different indicators from the databox and manually enter them into excel, but i would like to get those values along with OHLC into an Excel or CSV.
for example, i have BollingerBands, ExpMovingAverages, AverageVolume and PivotPoints. Can we tweak this code to extract these other values?
Thanks for your help
=

 

[mn88]

@korygill

If you dont mind me asking have you found any ways to use Python with Think or Swim? Or are you just using it to backtest with historical data? I am trying to learn Python also on https://www.quantconnect.com/ which can tie into a couple of different trading platforms but I havent seen anything for Python with Think or Swim. Also if you have found any good resources for learning how to use Python for trading you wouldnt mind sharing I would appreciate it.

The site I linked above looks cool for backtesting once I learn Python, it also has labs to help teach Python for trading for free if anyone else is interested.

Lastly thank you for this post, nice work!

 

[korygill]

@mn88
Have not made much progress learning Python. Still using PowerShell, and all my backtesting is outside of TOS.

 

[Azimuth]

Hi everyone,
I followed the steps, put the .csv and the .ps1 file in the same folder, went to Powershell and ran the command (while in my folder directory in Powershell) (first I had to enable scripts execution policy)
$data = .\Get-OHLC.ps1 .\StrategyReports_ESXCME_81919.csv

(except my file happens to be called _12120.csv at the end, so I updated that)

and I am getting the error: "You cannot call a method on a null-valued expression" and it just repeats over and over, so I hit the Break key. Does anyone know what could be causing this? I am running Windows 7.

 

[korygill]

@Azimuth

1) have you added the fix in post #10 if you are using daily data?

2) I have refactored this script from above, and i should post the new version, and how i run it. Not everyone knows how to run PowerShell, understandable, so maybe I'll even make a quick youtube on how to do this and link it here.

 

[Azimuth]

1) I think I will use daily data eventually, but at the outset I just used your original strategy. So I will apply the fix later. First I need to learn where to put the code, this is my first time using Think Script, so give me a day or two. I found this forum because I googled "is there any way to download OHLC from ThinkOrSwim". I am beginning to keep records Jesse Livermore style, and it would save a lot of time if I didn't have to mantually
2) I would appreciate that! Thank you.

I wonder if it might have to do with my ThinkOrSwim running on PST time zone?

 

[Azimuth]

Update: changing my ThinkOrSwim to EST time zone didn't fix; I hit the Break key quicker this time and saw that the first error is "Method invocation failed because [System.Collections.ArrayList] doesn't contain a method named 'new'." After that it will spam the "You cannot call a method on a null-valued expression" error.

 

[korygill]

I think this is failing @Azimuth because you are on Windows 7 and that version of PowerShell does not have ::new() for ArrayList. I have a few updated scripts, and plan on updating this post, but I am doubtful this will work on as Windows 7 machine.

 

[korygill]

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

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

#
# 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.

#
# 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

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

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

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