Exporting historical data from ThinkorSwim for external analysis

JonR

JonR

New member
@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.
 
S

sdtrader2

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

g00sey

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

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

b0rd2dEAth

New member
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!
 
Last edited:
J

judddder

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

korygill

Active member
VIP
It looks like you have an extra space.

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

MrBubbles

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

judddder

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

judddder

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

Chapter11

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

korygill

Active member
VIP
what you export is determined by variables and the formatted output line. read the code and find
Code:
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
Code:
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

SittingBullTrader

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

korygill

Active member
VIP
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:

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:

Code:
#
# 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
    }
}
 
Last edited by a moderator:

Similar threads

Top