Exporting historical data from ThinkorSwim for external analysis

FreshD

New member
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)
Is there a way to modify this code to pull historical options chain data for historical and expired contracts?
 

luxurious08

New member
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

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
 

korygill

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

Leom

New member
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(
    +         ~~~~~~~~~~~~~~~~~~~~~
 

korygill

Well-known member
VIP
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(
    +         ~~~~~~~~~~~~~~~~~~~~~
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.
 

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