Exporting historical data from ThinkorSwim for external analysis

[pypl]

Hi,
I wonder if you can modify the code to export only the last 2 bars instead of all bars on chart.
Thank you so much for the code posted.
HN

 

[akmohan]

@korygill -- What a clever hack ... Using Powershell is the best part .. no need to install PY/PERL.

Thank you very much !!!! Love it !!!!

I have to learn Poweshell now since I think it will help with a lot of data cleansing etc ...

 

[akmohan]

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

Question: Instead of reading into the memory array - why not print them directly to the file? That would save memory .. right?

 

[sargam409]

Your code in that rar file worked fine.

See also this post #20 for how to use it.
https://usethinkscript.com/threads/...nkorswim-for-external-analysis.507/post-14606
Run from directory the script and csv file are both in....

See the output:
.\Get-SOHLCP.ps1 -File .\StrategyReports_SPYV_2016.csv

Put output in a variable:
$data = .\Get-SOHLCP.ps1 -File .\StrategyReports_SPYV_2016.csv

Save to csv:
$data | ConvertTo-Csv -NoTypeInformation | Out-File -FilePath .\SPYV-Data.csv

Mr. Gill, I have successfully used the above script for historical data download on stocks - very helpful, thanks a lot! However, for mutual funds, this script is not plotting data on chart for further export. How do go I about modifying this script to work with Mutual funds? Thanks in advance for help!

 

[kevin_Boston]

This is cool.. i am trying to extract data from my chart with indicators.
Currentl, I get the values of different indicators from the databox and manually enter them into excel, but i would like to get those values along with OHLC into an Excel or CSV.
for example, i have BollingerBands, ExpMovingAverages, AverageVolume and PivotPoints. Can we tweak this code to extract these other values?
Thanks for your help
=

hi, I'm just tryin to dabble with whats discussed here..were you able to download OHLC along with indicators data? Apprecaite the response.

Hello everybody,

I m new here and first of all I want to thank u all involved on creating this great methode to export data from ToS. Especially big big Thank u to Kory Gill !

I have already tried the data export and everything worked perfectly.

In the next step I tried to add PointOfControl Data of VolumeProfile (ToS Study), but unfortually without success. I get always N/A displayed, although the drawing is working pretty well.
(code saved as Strategy)

I hope so much that some of u can help me in this case or give me a hint, workaround ... to get this data.

Many thanks in advance for ideas or suggestions.

############



Here the result that is display

View attachment 14174

Here the code i wrote (mainly copied from ToS and added Korys Code and saved as Strategy)




[CODE]#-------------------






input pricePerRowHeightMode = {default TICKSIZE, AUTOMATIC, CUSTOM};

input customRowHeight = 1.0;

input timePerProfile = {default "OPT EXP", CHART, MINUTE, HOUR, DAY, WEEK, MONTH, BAR};

input multiplier = 1;

input onExpansion = no;

input profiles = 1000;



input valueAreaPercent = 70;

input opacity = 20;

input Decimalstellen = 2;


def period;

def yyyymmdd = GetYYYYMMDD();


def seconds = SecondsFromTime(0);

def month = GetYear() * 12 + GetMonth();

def day_number = DaysFromDate(First(yyyymmdd)) + GetDayOfWeek(First(yyyymmdd));

def dom = GetDayOfMonth(yyyymmdd);

def dow = GetDayOfWeek(yyyymmdd - dom + 1);

def expthismonth = (if dow > 5 then 27 else 20) - dow;

def exp_opt = month + (dom > expthismonth);


switch (timePerProfile) {

case CHART:

period = 0;

case MINUTE:

period = Floor(seconds / 60 + day_number * 24 * 60);

case HOUR:

period = Floor(seconds / 3600 + day_number * 24);

case DAY:

period = CountTradingDays(Min(First(yyyymmdd), yyyymmdd), yyyymmdd) - 1;

case WEEK:

period = Floor(day_number / 7);

case MONTH:

period = Floor(month - First(month));

case "OPT EXP":

period = exp_opt - First(exp_opt);

case BAR:

period = BarNumber() - 1;


}


def count = CompoundValue(1, if period != getValue(period,1) then (getValue(count,1) + period - getValue(period,1)) % multiplier else getValue(count,1), 0);


def cond = count < getValue(count,1) + period - getValue(period,1);


def height;


switch (pricePerRowHeightMode) {

case AUTOMATIC:

height = PricePerRow.AUTOMATIC;

case TICKSIZE:

height = PricePerRow.TICKSIZE;

case CUSTOM:

height = customRowHeight;

}



profile vol = VolumeProfile("startNewProfile" = cond, "onExpansion" = onExpansion, "numberOfProfiles" = profiles, "pricePerRow" = height, "value area percent" = valueAreaPercent);


def con = CompoundValue(1, onExpansion, no);

def pc = if IsNaN(vol.GetPointOfControl()) and con then getValue(pc,1) else vol.GetPointOfControl();

def hVA = if IsNaN(vol.GetHighestValueArea()) and con then getValue(hVA,1) else vol.GetHighestValueArea();

def lVA = if IsNaN(vol.GetLowestValueArea()) and con then getValue(lVA,1) else vol.GetLowestValueArea();


def hProfile = if IsNaN(vol.GetHighest()) and con then getValue(hProfile,1) else vol.GetHighest();

def lProfile = if IsNaN(vol.GetLowest()) and con then getValue(lProfile, 1) else vol.GetLowest();

def plotsDomain = (IsNaN(close) == onExpansion);


def ProfileStart = if barNumber()==1 then 1 else if pc <> getValue(pc,1) then 1 else 0;



#-------------------


#POC Draw


#-------------------



input Show_Profile = {default "None", "All", "POC",

"Profile", "Profile with POC", "Profile with POC & Value",

"Profile with ProfileHighLow, POC",

"ProfileHighLow", "ProfileHighLow with POC",

"ValueArea", "ValueArea with POC"};


def ShowProfile = ((Show_Profile == Show_Profile."Profile" or Show_Profile == Show_Profile."Profile with POC"

or Show_Profile == Show_Profile."Profile with POC & Value"

or Show_Profile == Show_Profile."Profile with ProfileHighLow, POC"

or Show_Profile == Show_Profile."All")

and Show_Profile <> Show_Profile."None");


def ShowPOC = ((Show_Profile == Show_Profile."POC" or Show_Profile == Show_Profile."Profile with POC"

or Show_Profile == Show_Profile."Profile with POC & Value"

or Show_Profile == Show_Profile."Profile with ProfileHighLow, POC

or Show_Profile == Show_Profile."ProfileHighLow with POC"

or Show_Profile == Show_Profile."ValueArea with POC"

or Show_Profile == Show_Profile."All")

and Show_Profile <> Show_Profile."None");


def ShowValueArea = ((Show_Profile == Show_Profile."ValueArea" or Show_Profile == Show_Profile."ValueArea with POC"

or Show_Profile == Show_Profile."All")

and Show_Profile <> Show_Profile."None");


def ShowProfileHighLow = ((Show_Profile == Show_Profile."ProfileHighLow" or Show_Profile == Show_Profile."ProfileHighLow with POC"

or Show_Profile == Show_Profile."Profile with ProfileHighLow, POC"

or Show_Profile == Show_Profile."All")

and Show_Profile <> Show_Profile."None");



DefineGlobalColor("Profile", Color.CYAN);

DefineGlobalColor("Point Of Control", GetColor(5));

DefineGlobalColor("Value Area", GetColor(8));


vol.Show(if Show_Profile then GlobalColor("Profile") else Color.CURRENT, if ShowPOC then GlobalColor("Point Of Control") else Color.CURRENT, if ShowValueArea then GlobalColor("Value Area") else Color.CURRENT, opacity);


plot POC = if plotsDomain and ShowPOC then pc else Double.NaN;

POC.SetPaintingStrategy(PaintingStrategy.HORIZONTAL);


plot ProfileHigh = if plotsDomain and ShowProfileHighLow then hProfile else Double.NaN;

ProfileHigh.SetPaintingStrategy(PaintingStrategy.HORIZONTAL);


plot ProfileLow = if plotsDomain and ShowProfileHighLow then lProfile else Double.NaN;

ProfileLow.SetPaintingStrategy(PaintingStrategy.HORIZONTAL);


addChartBubble(profileStart and Show_Profile == Show_Profile."Profile with POC & Value", pc, pc, color.white, no);



###################################################################



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.Black,

Color.Black,

name="Symbol_POC|"+GetSymbol()+"|" + pc[-1]);    # <<< here i put pc (Point Of Control) as data to be displayed but i get only N/A


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

New to investing: 1 year[/CODE]

were you ever able to get the N/A resolved for custom indicator values? I'm just couple years behind on this...

 

[daisy101]

Is it possible to get volume?

 

[kevin_Boston]

There is no automatic exporting feature in TOS.

are any any TOS API's that can call the strategy and download the files to cloud?

 

[useThinkScript]

are any any TOS API's that can call the strategy and download the files to cloud?

Sadly no. The useThinkScript forum does not have any ToS APIs.
Nor supports the creation of them.

 

[Jfeldm12]

How do you add volume to the code?

 

[sftong]

How do you add volume to the code?

Use function "volume[-1]" or "volume[0]". Just play with the index number 0, -1, 1, to get to the right time

 

[sftong]

@korygill

Hey Kori or anyone, as of March 1, when I open the ThinkOrSwim Desktop app, using Kory's script as usual to export into CSV file and then parse it. However, this time I consistently run into an issue of the Date format. The parsing script " $data | ConvertTo-Csv -NoTypeInformation | Out-File -FilePath D:\ThinkSwim\***.csv" consistenly error out with a message:

"Exception calling "Parse" with "1" argument(s): "The string was not recognized as a valid DateTime. There is an unknown word starting at index 13."
At D:\ThinkSwim\Get-SOHLCP.ps1:56 char:9

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

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

After some diagnosis, I found that there is likely a HIDDEN character somewhere in the exported CSV that the parsing script could not handle. Example below:

Id;Strategy;Side;Amount;Price;Date/Time;Trade P/L;P/L;Position;
1;ExportSPX(SellClose);Sell to Close;-1.0;$23.98;1/31/25, 10:00 AM;$0.18;$0.26;-1.0;
2;ExportSPX(SOHLCP|LI|23.84|23.84|23.58|23.72|23.84|272,096);Buy to Open;1.0;$23.80;1/31/25, 10:00 AM;;$0.18;0.0;

In line"2;", the timestamp "10:00 AM" likely has a character hidden in the space between the last "0" and the 1st letter "A". In other words, something "X" is hidden here "1/31/25, 10:00XAM".

If I remove the space and re-insert the keyboard space there, then the parsing script would run successfully. I have restarted my PC, restarted TOS Desktop app, Remove the Study and Re-include the Study into TOS, and still the same issue and observation.

I mean, I have been running this for over 2 years and absolutely no issue. Just starting to happen today. Anyone has any thoughts?

Thanks
Sean

 

[mii]

I am getting many parse errors. anyone has the latest scripts?
Exception calling "Parse" with "1" argument(s): "The string was not recognized as a valid DateTime. There is an
unknown word starting at index 14."

 

[truthntrading]

I am getting many parse errors. anyone has the latest scripts?
Exception calling "Parse" with "1" argument(s): "The string was not recognized as a valid DateTime. There is an
unknown word starting at index 14."

Hey Mii were you able to resolve this? I'm getting Parse errors on line 13 myself. same error.

Thanks for letting me know!

 

[mii]

Hey Mii were you able to resolve this? I'm getting Parse errors on line 13 myself. same error.

Thanks for letting me know!

no, its not working.

 

[JohnAdams]

Good morning, everyone. This is my first post, and I signed up because of this thread. I wanted to share an Excel sheet that uses a macro. You can easily choose the import file, and it will create a new sheet with the data organized. Kudos to the OP for this! I had been searching for a way to get option data from Thinkorswim for a while. It's posts like this that really help people access data, etc. I understood the previous instructions, and everything worked great, but I noticed many people struggle a bit with it and thought I would help the community.

For those wondering about options data, the sheet contains an export file of SPY straddle. I would prefer bid or ask data for SPY options, but I had issues using a combined ticker in the chart pane that included bid or ask. To get the options data export, just type the options ticker in the chart as the main instrument and export. I also found you don’t need the orders shown on screen to export, which is nice.
The instructions are:

Open Excel file click import and format data
Choose the file, and it will make a new sheet
Done

Enjoy!!

 

[korygill]

Looking at this in the debugger, it does seem the format of the file now includes a different format for the Date with an added comma and some unprintable unicode character before the "AM" or "PM".

Can see this when looking at the strategy report csv file in Notepad and/or Visual Studio Code and the debugger.

Fix seems as easy as removing these unicode characters.

        # Remove non-ASCII characters (e.g., hidden Unicode spaces)
        $cleanDateTime = [System.Text.RegularExpressions.Regex]::Replace($item.'Date/Time', '[^\x00-\x7F]', ' ').Trim()

        # add to our $outData array
        $null = $outData.Add(
            [PSCustomObject]@{
                'DateTime' = ([datetime]::Parse($cleanDateTime))
                'Open' = [decimal]$open
                'High' = [decimal]$high
                'Low' = [decimal]$low
                'Close' = [decimal]$close
                }
            )

@sftong @mii @truthntrading