Exporting historical data from ThinkorSwim for external analysis

korygill

Well-known member
VIP
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

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

Code:
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.

Code:
[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

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

Attachments

  • hmxvHwp.png
    hmxvHwp.png
    183.1 KB · Views: 256
  • DrPnZQm.png
    DrPnZQm.png
    207.1 KB · Views: 213
Last edited:

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

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!
 
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.
 
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.
 
Does anyone have a template code for profit/loss for use in TOS charts? It will be very handy for doing backtests.
 
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 .
 
@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
 
Yeah, that's an oversight in the original script.

Replace this line
Code:
# 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:
Code:
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;
 
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
=
 
@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!
 
@mn88
Have not made much progress learning Python. Still using PowerShell, and all my backtesting is outside of TOS.
 
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.
 
@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.
 
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?
 
Last edited:
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.
 
Last edited:
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.
 
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
 
Last edited:

Similar threads

Not the exact question you're looking for?

Start a new thread and receive assistance from our community.

87k+ Posts
217 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

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