Exporting historical data from ThinkorSwim for external analysis

[ssaeed73]

Right but is there a way to export the watchlist every minute? Any APIs I can use to access the watchlist to get the data to another program maybe?

 

[BenTen]

Not that I know of.

 

[Laser71]

Does Thinkscript have the ability to create the following scans.
1. Retrieve Daily Data for a certain ticker for the past year. IE OHLC?
2. Scan the entire market for the past year to find all tickers the gapped up 70% or more?

 

[korygill]

I am unable to export data using the method from my post here for the SPX. Works for other symbols, but not for SPX which is not a tradeable symbol, but for back testing purposes, this was a great index to use.

Anyone else noticing this?

 

[BenTen]

Same here. It looks like they drop support for backtesting script altogether for this particular ticker. I guess the alternative would be to use $SPY.

 

[Tapir]

Update on 9/25/2020

I get a lot of requests for how to add Volume to the study, so I have made an update to the scripts for Volume. The important thing to notice/understand is how you add data to the TOS script separated by the | vertical bar and then keeping the PowerShell script in sync with the ordering of the fields/columns/data you are encoding on TOS in the Buy Order so they can be parsed meaninfully.


See my YouTube video with a walk through of how to export historical data and run a PowerShell script to generate a proper csv file. Also includes adding additional data like other indicators to the csv data.

See previous posts for more information as well about exporting historical data.
https://usethinkscript.com/threads/...nkorswim-for-external-analysis.507/post-14606
and
https://usethinkscript.com/threads/...m-for-external-analysis.507/page-2#post-17387


Video:

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 = 1;#if agg >= AggregationPeriod.DAY then 1 else if SecondsTillTime(adjEndTime) >= 60 and SecondsFromTime(adjStartTime) >= -60 then 1 else 0;

# get indicator values
#def macdValue = MACD().Value;
#def macdAvg = MACD().Avg;

# if you want macdValue and macdAvg, replace the name= line in the forula with
# name="SOHLCP|"+GetSymbol()+"|"+open[-1]+"|"+high[-1]+"|"+low[-1]+"|"+close[-1]+"|"+close+"|"+volume[-1]+"|"+macdValue+"|"+macdAvg);
# but you also need to keep the PowerShell script in sync to parse the line properly

AddOrder(OrderType.BUY_TO_OPEN,
    marketOpen,
    low,
    1,
    Color.White,
    Color.White,
    name="SOHLCP|"+GetSymbol()+"|"+open[-1]+"|"+high[-1]+"|"+low[-1]+"|"+close[-1]+"|"+close+"|"+volume[-1]);

AddOrder(OrderType.SELL_TO_CLOSE, marketOpen, high, 1, Color.White, Color.White, name="SellClose");

PowerShell code for Get-SOHLCP.ps1:

#
# Get-SOHLCP.ps1
#
# Script to convert a TOS ThinkOrSwim kg_EveryTickSOHLCP strategy report csv file to an object and/or proper csv data file.
#
# Author: Kory Gill, @korygill
# Version: 20200304.2300
#
# Examples
# $x = gci 'StrategyReport*' | Select -ExpandProperty FullName | D:\Source\Repos\technical-analysis-dev\dev\Get-SOHLCP.ps1 -InformationAction Continue -ExportAsObject
# $x = gci 'D:\Database\MarketData\TOS-StrategyReport-Files-SOHLCP\feb-3to7\StrategyReport*' | Select -ExpandProperty FullName | D:\Source\Repos\technical-analysis-dev\dev\Get-SOHLCP.ps1 -InformationAction Continue
# $x = gci 'D:\Database\MarketData\TOS-StrategyReport-Files-SOHLCP\feb2020\StrategyReport*' | Select -ExpandProperty FullName | D:\Source\Repos\technical-analysis-dev\dev\Get-SOHLCP.ps1 -InformationAction Continue
# $x = gci 'D:\Database\MarketData\TOS-StrategyReport-Files-SOHLCP\UseThinkScriptDemo\StrategyReport*' | Select -ExpandProperty FullName | D:\Source\Repos\technical-analysis-dev\dev\Get-SOHLCP.ps1 -InformationAction Continue
#
# Get the symbol back:
# $m = 'SOHLCP-(~ES.XCME)(1.21.20 5.00 AM - 1.27.20 1.14 PM).csv' | Select-String -Pattern '\((.*?)\)'  -AllMatches; $m.Matches[0].Groups[1].Value -replace '~', '/'
#
# $f = gci D:\temp\TOS-StrategyReport-Files-SOHLCP\sohlcp*.csv | Select -ExpandProperty FullName; $f | % {$m = $_ | Select-String -Pattern '\((.*?)\)'  -AllMatches; $s = $m.Matches[0].Groups[1].Value -replace '~', '';$sym = ($s -split '\.' | Select -First 1); $file = $_; D:\Source\Repos\technical-analysis-dev\dev\Get-AllOrbTrades-SOHLCP.ps1 -FileName $file -SymbolName $sym}
# gci *glArray.csv | % {$csv = Import-Csv -Path $_; $csv | ? {[DateTime]::Parse($_.Datetime).Month -eq 1} | Export-Csv -NoTypeInformation -Path All_glArray_Data.csv -Append}
# get all January (month 1)
# gci D:\Database\MarketData\TOS-StrategyReport-Files-SOHLCP\feb-3to7\*glArray.csv | Select -ExpandProperty FullName | % {$csv = Import-Csv -Path $_; $csv | ? {[DateTime]::Parse($_.Datetime).Month -eq 1} | Export-Csv -NoTypeInformation -Path D:\Database\MarketData\TOS-StrategyReport-Files-SOHLCP\feb-3to7\All_glArray_Data.csv -Append}
#
[CmdletBinding()]
param (
    [Parameter(Mandatory=$true, ValueFromPipeline=$true)]
    [string[]]
    $File,
    [switch]
    $ExportAsObject
)
Begin {
    function Convert-CurrencyStringToDecimal ([string]$input)
    {
        ((($input -replace '\$') -replace '[)]') -replace '\(', '-') -replace '[^-0-9.]'
    }
    $global:sohlcpAllData = New-Object System.Collections.Generic.List[PSCustomObject]
}
Process {
    foreach ($f in $File)
    {
        if (-not (Test-Path $f))
        {
            throw "Cannot open file '$f'."
        }
        Write-Information "Processing file: '$f'."
        # read csv file
        $content = Get-Content -Path $f
        # generate filename
        $csvSymbol = ($content[1] -split 'Symbol: ')[1] -replace '/', '~' -replace ':', '.'
        $csvWorkTime = ($content[2] -split 'Work Time: ')[1] -replace '/', '.' -replace ':', '.'
        $outFile = 'SOHLCP-(' + $csvSymbol +')('+ $csvWorkTime + ')'
        # 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"}
        $sohlcpFileData = New-Object System.Collections.Generic.List[PSCustomObject]
        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
            $volume = $v[6] | Convert-CurrencyStringToDecimal
            #
            # Depending on how you gather data in your TOS script determines how you extract the values all separated by the | vertical bar symbol.
            #
            <#
            $sohlcpData = [PSCustomObject]@{
                'Symbol' = $symbol
                'DateTime' = ([datetime]::Parse($item.'Date/Time'))
                'Open' = [decimal]$open
                'High' = [decimal]$high
                'Low' = [decimal]$low
                'Close' = [decimal]$close
                'PrevClose' = [decimal]$prevClose
                'Volume' = [decimal]$volume
                'macdValue' = [decimal]$macdValue
                'macdAvg' = [decimal]$macdAvg
                }
            #>
            $sohlcpData = [PSCustomObject]@{
                'Symbol' = $symbol
                'DateTime' = ([datetime]::Parse($item.'Date/Time'))
                'Open' = [decimal]$open
                'High' = [decimal]$high
                'Low' = [decimal]$low
                'Close' = [decimal]$close
                'PrevClose' = [decimal]$prevClose
                'Volume' = [decimal]$volume
                }
            # add to our $sohlcpData array
            $null = $sohlcpFileData.Add($sohlcpData)
            $null = $sohlcpAllData.Add($sohlcpData)
        }
        # save to file
        $sohlcpFileData | Export-Csv -Path (Join-Path (Split-Path -Path $f -Parent) ($outFile + '.csv')) -Force -NoTypeInformation -Encoding ASCII
    }
}
End {
    if ($ExportAsObject)
    {
        # helpful message to show caller our output variable
        Write-Information "Out Data $($sohlcpAllData.Count) items (exported as `$sohlcpAllData)"
    }
    else
    {
        # don't show any extraneous output, and just return the data to the pipeline
        return $sohlcpAllData
    }
}

I don't know what I'm doing wrong but powershell gives me everytime "Cannot bind argument to parameter 'ImputObject' because it is null". It creates the new .csv files, but they are empty. Could you please help me?

 

[korygill]

For anyone to help you @Tapir you'd need to show what you did, the error messages, etc. Perhaps screenshots or a video.

 

[Tapir]

Ok, thank you a lot for your help.

So basically I copied the ToS studio just as it is in the last update with the volume. The time-frame I'm using is 2D:1m

Everything seems fine. I export the file of the report successfully and I save it on C:\Users\david\Desktop\ThinkExports

In that same folder I've created "Get-SOHLCP.ps1" also exactly the same code you posted for including volume:

I run Powershell as administrator for removing the restrictions, after that I close it and I run it normally for running this:

$x = gci 'C:\Users\david\Desktop\ThinkExports\StrategyReport*' | Select -ExpandProperty FullName | C:\Users\david\Desktop\ThinkExports\Get-SOHLCP.ps1 -InformationAction Continue

This is the result:

For anyone to help you @Tapir you'd need to show what you did, the error messages, etc. Perhaps screenshots or a video.

 

[korygill]

I just ran this on a freshly exported copy of AAPL.

C:\Users\koryg\Desktop> $x = gci 'C:\users\koryg\Desktop\StrategyReports_AAPL_121720.csv' | Select -ExpandProperty FullName | D:\Source\Repos\technical-analysis-dev\dev\Get-SOHLCP.ps1 -InformationAction Continue
Processing file: 'C:\users\koryg\Desktop\StrategyReports_AAPL_121720.csv'.
C:\Users\koryg\Desktop>

Can also run like this and maybe see more output:

gci 'C:\users\koryg\Desktop\StrategyReports_AAPL_121720.csv' | Select -ExpandProperty FullName | D:\Source\Repos\technical-analysis-dev\dev\Get-SOHLCP.ps1 -InformationAction Continue

Notice you can supply the exact filename as well.
C:\users\koryg\Desktop\StrategyReports_AAPL_121720.csv

 

[LewisM20]

Hey @Tapir,

I had the same error. You need to change your Windows Date & Time format to "English (United States)".

@korygill Thank you for the codes!

 

[Tapir]

Hey @Tapir,

I had the same error. You need to change your Windows Date & Time format to "English (United States)".

@korygill Thank you for the codes!

Thanks! It was exactly that, I tried to change it yesterday but I was doing it wrong. Now everything works perfectly.

 

[korygill]

Hey @Tapir and @LewisM20 and others,
Good catch! Yeah, the parser parses US date/time. I can look to update it to recognize int'l formats. Guess it depends on how TOS exports the time format for different locales.

 

[korygill]

@Tapir and @LewisM20, post the first few lines of your TOS export file. Curious what you all see in that file? PowerShell should be parsing the dates, and wondering how TOS is exporting this.

Example:

Strategy report
Symbol: AAPL
Work Time: 12/16/20 1:01 AM - 12/17/20 4:55 PM


Id;Strategy;Side;Amount;Price;Date/Time;Trade P/L;P/L;Position;
1;kg_EveryTickSOHLCP(SellClose);Sell to Close;-1.0;$128.20;12/16/20 1:01 AM;$0.12;($0.14);-1.0;
2;kg_EveryTickSOHLCP(SOHLCP|AAPL|128.15|128.35|128.15|128.34|128.2|3,068);Buy to Open;1.0;$128.08;12/16/20 1:01 AM;;$0.12;0.0;

 

[LewisM20]

Strategy report
Symbol: SPY
Work Time: 12/18/20 8:25 AM - 12/18/20 4:00 PM


Id;Strategy;Side;Amount;Price;Date/Time;Trade P/L;P/L;Position;
1;shared_kg_EveryTickOHLC(SellClose);Sell to Close;-1.0;$370.80;12/18/20 8:25 AM;$0.13;$0.11;-1.0;
2;shared_kg_EveryTickOHLC(SOHLCP|SPY|370.68|370.73|370.6202|370.69|370.69|-0.036|0.0106);Buy to Open;1.0;$370.68;12/18/20 8:25 AM;;$0.13;0.0;

Hey Kory,

TOS exports in United States Date & Time. When I ran the script in the beginning, the excel file generated was empty. So I compared the "Work Time" on the TOS export to my Windows time, and it was because my Windows Date & Time was in Canadian format.

Thanks again.

 

[brandoita]

First at all i would like to thank you @korygill for all this work! Amazing.

Do you know a way to convert the csv to metastock format? Or maybe to an ASCII?
I would like to use this data on adv get 7.8.

Thanks!!

 

[korygill]

@brandoita, the output is csv which is a text file, can be imported into excel, or you can convert into whatever format you want. CSV is already an ASCII file.

 

[brandoita]

Thanks for your reply @korygill!

I don't know why Advanced GET don't recognize the files. In addition I tried to use the Metastock's Dowloader to make the convertion from ASCII to metastock but again I have the same situation.

Will continue trying if I have some news I'll let you know.

Thanks again!

 

[aragona]

Is it possible to use think script to save or export the prices from lines drawn on the chart to a file or a database? An example would be prices from a Fibonacci draw not he charts and other support and resistance lines exported to a file?

 

[BenTen]

@aragona Not possible.

 

[Davebrubeck]

I'm trying to add the VWAP, does anybody know how to do it? I've tried but I'm not sure if I'm making a mess with the powershell code.