Exporting historical data from ThinkorSwim for external analysis

[pjk]

@korygill, thank you so much for figuring out and documenting how to export data from TOS. I am unfortunately stuck after pasting your code from post #20 and adding it as a study to a 3D 1min chart. I have pasted screenshots of the steps since it may help others.

Ultimately I want to export daily or weekly data but I want to get the scripted example from your video working first.

 

[BenTen]

@pjk You need to add the script as a strategy, not as a new study.

 

[YummyTrade]

First post, apologies if this has already been asked.
Is is possible to write a thinkscript that automatically runs on a schedule, say every 10 minutes, and automatically output the results to csv/or excel. Thank you!

 

[rad14733]

@YummyTrade No...

 

[YummyTrade]

@YummyTrade No...

Thanks for the confirmation. On to the api then.

 

[Chapter11]

I'm trying to decode the TTM Squeeze/Momentum Indicator for some backtesting in MySQL or Excel. I've tried Mobius', LazyBear and other scripts- but converting them over into MySQL or EXCEL I have never been able to match up the numbers even remotely close to those given in TOS.

What I need is a formula in Excel or MySQL that will give me the same numbers for MOMENTUM as what TOS gives...

Has anyone been successful finding the correct code for this? I believe the problem lies in the Linear Regression portion, but have even tried Standard Deviation and others but no luck.

Thank you for your input!

 

[rad14733]

@Chapter11 the TTM_Squeeze and/or TTM_Squeeze_Pro code the Inertia() function

 

[Chapter11]

Thank you for the reply. Yes, I practically have the code memorized by this point!
I'm using MySQL as I have 15yrs (excessive, I know) of 1-min chart data, and I want to run stats/probabilities on specific indicators and combination of indicators in conjunction with basic candle patterns/TA. TOS simply can't do that. Once I get my answers as to what has the highest 'odds', then I can take it over and run it thru the TOS strategy...

I've tried many different versions of the squeeze codes out there, as well as my own. NONE of them come close to what TOS is spitting out. I think I figured out the PROBLEM- it's coming down to what I'm using for 'X'. I'm using 1-min charts, so each row is a 1min candle. If I use '1' for X... it throws null errors in the results. If I use '20' for X, the numbers are much too big. I can get close using something around 1.325- but again, it's not exact...

The BIGGER problem is, the PATTERN of the momentum study doesn't match up. Meaning- with the resulting Inertia solution- the numbers turn negative/positive (ie, flip the zero-line on a histogram) about 5min off from what it shows in TOS. That won't work...

Oh man- seriously have spent 2-3 weeks on this, full time. I'm just not getting it. I'd be ok if the numbers were off slightly, but, the 'pattern' matched that of TOS... but nope- not even usable....

Any hints or suggestions for me??

 

[Davebrubeck]

This code works amazingly well, thanks! Would it be possible to have something to export automatically the data of a list of tickers? It takes a lot of time to do it manually one by one, with that this would be the best.

 

[BenTen]

This code works amazingly well, thanks! Would it be possible to have something to export automatically the data of a list of tickers? It takes a lot of time to do it manually one by one, with that this would be the best.

There is no automatic exporting feature in TOS.

 

[Tiredoflosing]

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?

hey, just following up on this, have you found the Livermore method useful? I just finished the book and thought about doing the same.

 

[meadisgood]

First, I just want to say a huge thank you to korygill for this code. I've actually been using it to export historical data and then feed that into R for some analysis.

I have something I can't seem to figure out though. I'm trying to add the current VWAP (and possibly devs up/dn) values for each one-min bar to the report. I've read through the ThinkScript tutorials but still no joy. I tried to simply add it to the "name" part of the AddOrder section like so:

name="SOHLCP|"+GetSymbol()+"|"+open[-1]+"|"+high[-1]+"|"+low[-1]+"|"+close[-1]+"|"+close+"|"+vwap[-1]);

But unfortunately that doesn't give the correct values.

 

[redaresg]

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.

Hi, I am trying to run the script in PowerShell and am getting the error message: .\Get-OHLC.ps1 : The term '.\Get-OHLC.ps1' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. At line:1 char:9 + $data = .\Get-OHLC.ps1 + ~~~~~~~~~~~~~~ + CategoryInfo : ObjectNotFound: (.\Get-OHLC.ps1:String) [], CommandNotFoundException + FullyQualifiedErrorId : CommandNotFoundException

 

[redaresg]

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

Hi Kory and thanks for this useful scripts!
I have successfully managed to execute steps #1 + #2, but now when I run the PS script I am getting this error for all the records:

Exception calling "Parse" with "1" argument(s): "String was not recognized as a valid DateTime."
At C:\HistoricalData\Get-OHLC.ps1:44 char:9

• $null = $sohlcpData.Add(
• ~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo : NotSpecified: ) [], MethodInvocationException
+ FullyQualifiedErrorId : FormatException

 

[ToS4life2021]

@Davebrubeck

Any luck with the VWAP? Would be quite nice being able to get that data.

 

[korygill]

@redaresg is your date time format different from the US format?
What do the first few lines of your csv file look like?
Example:

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

And if you take a sample time like in that csv file and enter something like the below in your PowerShell cmd window, what happens?

C:\> [datetime]::Parse("1/17/2020 6:30:00 AM")

Friday, January 17, 2020 6:30:00 AM


C:\>

 

[meadisgood]

@Davebrubeck

Any luck with the VWAP? Would be quite nice being able to get that data.

Not sure if maybe that was meant for me, but I have not had luck with the VWAP data export yet. Unfortunately.

 

[arm1]

This is an awesome workaround. My issue is that the name will only show up to 4 digits after the decimal for values of a custom study I put in, but I need to get the full 7 digits after the decimal points. Does anyone know a workaround for this?

The lower study of my script will show 7 digits, but unfortunately there is not a simple export option here.

 

[mashume]

This is an awesome workaround. My issue is that the name will only show up to 4 digits after the decimal for values of a custom study I put in, but I need to get the full 7 digits after the decimal points. Does anyone know a workaround for this?

The lower study of my script will show 7 digits, but unfortunately there is not a simple export option here.

can you multiply the resultant values by 1000 and then divide by 1000 outside of ToS so that you get your necessary precision at the 4 decimal places ToS will export?

-mashume

 

[hQrGrIg8lY]

https://usethinkscript.com/threads/...-thinkorswim-for-external-analysis.507/page-3

I see a previous post about extracting data from ToS, but it is not for Mac OS. Can anybody show the best way to extract data, e.g., IMP_VOLATILITY(), from all optionable tickers from ToS on Mac OS?