Exporting historical data from ThinkorSwim for external analysis

[JonR]

@Picard I haven't tried to import data into a trading platform yet. I use the exported data in Excel to validate custom studies I create in TOS.

 

[sdtrader2]

Hi Folks,
I am a new user here. Great community here.
I am looking to write out historical daily closing value of $ADD from think of swim for the past 1 year.
Will appreciate the advice on this

Thanks

 

[g00sey]

@korygill thank you so much for this code! If I want to collect 5min bar data instead, would I only need to change how the variable marketOpen is defined in the strategy code, specifically changing 60 seconds to 300 seconds?

def marketOpen = if agg >= AggregationPeriod.DAY then 1 else if SecondsTillTime(adjEndTime) >= 300 and SecondsFromTime(adjStartTime) >= -300 then 1 else 0;

 

[b0rd2dEAth]

If anyone is interested, I adjusted the thinkScript strategy and the Powershell script to include volume. I have a screenshot but don't know how to include that in posts to the forum.

can please you or someone else please share the scripts with me? Im trying to get the data to include volume.

Im also trying to make sure the times go from 6:00am PST to 4:00pm PST and collect with minute iterations. does that mean I set the time to from 900 to 1900?

edit: for some reason ThinkOrSwim freezes when right clicking and clicking 'show report'. All I did was attempt to add in the volume. it worked for a small range. i'm trying to do 30 days, 1 minute intervals. is this a normal issue?

update: Resolved!
looks like the code was simpler than I thought. The only thing I had to do was boot into Windows and increase the memory size for ThinkOrSwim. Might work on macOS too, I'll try it later. Got my volume data loaded properly now. I gotta say, this code is quite elegant.

thanks korygill!

 

[korygill]

@g00sey just run the code on a 5m chart.

 

[judddder]

Kory,

I am struggling with the Powershell Portion of this. I keep getting an error trying to do this: From a PowerShell command window, run the script and pass your exported csv file as a parameter.

This is what I get:
PS C:\Users\jud> C:\Users\jud\Documents\Get-SOHLCP.ps1 C:\Users\jud\Documents\ StrategyReports_ESXCME_72720.csv
C:\Users\jud\Documents\Get-SOHLCP.ps1 : A positional parameter cannot be found that accepts argument 'StrategyReports_ESXCME_72720.csv'.
At line:1 char:1

• C:\Users\jud\Documents\Get-SOHLCP.ps1 C:\Users\jud\Documents\ Strateg ...
• ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo : InvalidArgument: ) [Get-SOHLCP.ps1], ParameterBindingException
+ FullyQualifiedErrorId : PositionalParameterNotFound,Get-SOHLCP.ps1

 

[korygill]

It looks like you have an extra space.

C:\Users\jud\Documents\Get-SOHLCP.ps1 C:\Users\jud\Documents\StrategyReports_ESXCME_72720.csv

 

[MrBubbles]

Hello Kory, appreciate the work you’ve done in this. Would it be possible to include volume data in this export?

 

[judddder]

It looks like you have an extra space.

C:\Users\jud\Documents\Get-SOHLCP.ps1 C:\Users\jud\Documents\StrategyReports_ESXCME_72720.csv

That was it, that seemed to just open the excel file though. Does it save the data somewhere?

 

[judddder]

That was it, that seemed to just open the excel file though. Does it save the data somewhere?

Nevermind, got it figured out. I just added semicolons instead of bars between data and import straight into excel using the From text/CVS button.

 

[Piper792]

@korygill I am trying to backtest price levels that I identify using TA. Can I export these price levels that I manually enter in a similar fashion to this approach?

I can then use the 1-minute data identified here to track Draw Down, Take Profit levels or percentages gained.

https://www.lockeinyoursuccess.com/connecting-thinkorswim-to-excel-part-1-of-4/

 

[Chapter11]

Hey @korygill!
Great info. I was already downloading historical and manually adjusting the files to import when I found this... I've never used PowerShell before- so this looks awesome. Thank you for putting it together!

Question for you- in your video you show adding MacD values to the file- but I never saw you add it in the thinkscript? Was it already in there and you just adjusted the file to pull those values in?

Thanks again!

 

[korygill]

what you export is determined by variables and the formatted output line. read the code and find

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

which has just open, high, low, close.

This is commented out and an example, while the actual code has macd in it by formatting as

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

in the AddOrder string which we use to "encode" information.

You could add Volume using this pattern or any other indicator value you can calculate in a script.

 

[SittingBullTrader]

JonR,

I also adapted the script to include volume; however, in order for most trading platforms to import this data the numerical values have to be in a numerical format not in a text format as this script generates. You can tell the format by seeing that the numbers are enclosed in quotation marks. Also the date/time field needs to be separated into a date field and a time field separately, not date & time combined. Possibly my previous post didn't clarify that. Have you tried to import your data into a trading platform yet?

Script for exporting volume.
https://mega.nz/file/4tckTQSB#voXExrndNk_PvDMC8880XjioVl946FVAu_i_uRZ4HZ0

Can you help me with the TOS study to report the Volume? I have download your files. I see that you modify the SOHLCV file with the volume code. I dont understand how fetch volume from tos to your .CVS file. Please share. Thanks bro.

 

[korygill]

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

 

[krazyKarl]

I'm new to thinkscripts and want to output the data with seconds as I'm getting multiple lines with same time, which messes up my back testing. here is an example:

UVXY,"10/28/2020 8:28:00 AM","21.2","21.23","21.18","21.23","21.2"

UVXY,"10/28/2020 8:30:00 AM","21.23","21.27","21.16","21.16","21.23"

UVXY,"10/28/2020 8:30:00 AM","21.16","21.23","21.15","21.2","21.16"

UVXY,"10/28/2020 8:30:00 AM","21.2","21.24","21.16","21.17","21.2"

UVXY,"10/28/2020 8:30:00 AM","21.17","21.3","21.16","21.289","21.17"

UVXY,"10/28/2020 8:31:00 AM","21.2899","21.38","21.2737","21.35","21.289"

UVXY,"10/28/2020 8:31:00 AM","21.35","21.38","21.31","21.35","21.35"

UVXY,"10/28/2020 8:31:00 AM","21.3482","21.35","21.275","21.3","21.35"

UVXY,"10/28/2020 8:31:00 AM","21.3","21.31","21.2601","21.3","21.3"

UVXY,"10/28/2020 8:32:00 AM","21.29","21.3","21.22","21.23","21.3"

UVXY,"10/28/2020 8:32:00 AM","21.2399","21.34","21.2304","21.34","21.23"

UVXY,"10/28/2020 8:32:00 AM","21.34","21.35","21.28","21.28","21.34"

UVXY,"10/28/2020 8:32:00 AM","21.28","21.32","21.26","21.32","21.28"

UVXY,"10/28/2020 8:32:00 AM","21.32","21.39","21.32","21.39","21.32"

can thinkscripts do seconds, I looked in the doc and didn't find what I wanted

 

[korygill]

@krazyKarl are you using a 1m chart or a tick chart? the script should have a line for every bar on the chart.

 

[SittingBullTrader]

@krazyKarl are you using a 1m chart or a tick chart? the script should have a line for every bar on the chart.

This way of getting data is very good. However, if you have a account with Trade Station you can also pull the data, anytime frame or indicators and save as .XLSX file.

 

[ssaeed73]

I have a scan running in ToS that populates a watchlist if stocks meet a certain criteria. My issue is that after a minute these stocks are removed from the watchlist as they no longer meet the criteria I am looking for, for example RSI under 30, but I would like to see what stocks had met those conditions.

Is it possible to setup the scan or the watchlist to keep a running tab of stocks that met my conditions or is there a way to programmatically export the list every minute or so to a text file I can view?

 

[BenTen]

I don't think you can modify the time interval.