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?