Exporting ToS data to Excel using the RTD Function.

[craigjjmorrison]

I track my portfolio in Excel with the RTD Function, however, ETFs do not seem to be recognized by the function. Any suggestions? I apologize if this question is outside the scope of this forum. I searched and found references to Excel but the search feature sent me to google for ETFs which I had already tried. I'd rather not fiddle with TOS's API if avoidable.

 

[XeoNoX]

you should probably call TOS support, its either a bug or they will explain why they dont have let you accomplish this with ETFs

 

[craigjjmorrison]

Thanks, I'm sure you're correct.

 

[craigjjmorrison]

@XeoNoX No response at all from TOS Support. However, the issue self-corrected. I think it was a matter of closing both apps and reopening in sequence (TOS then Excel), which is something I should have realized. Thanks again for your response.

 

[ozio72]

I am new to Think or Swim and this forum. I would like to perform statistical analysis on exported Market-Watch list data with Excel.

I export my Market-Watch list to Excel for analysis and get updated dynamic values of various boiler plate default parameters. My charts are set to display candlesticks. The Open, High and Low columns in my watch list are static and only change if a value is positively or negatively exceeded.

Is there an Excel RTD command code that will capture the OPEN, HIGH, LOW and CLOSE values of each candle that is plotted dynamically in the chart?

I generally use a chart time interval of 1 Day 1 minute. I use style box float data to see a variety of parameters that include the OPEN, HIGH, LOW and Close for a particular candle, displayed as I hover over a candle with my mouse. I would like to export each candle stick data group (OHLC) into Excel dynamically, as I do the other parameters in my Market Watch list.

Any assistance would be appreciated.

 

[mashume]

I think it depends on the timeframe you wish to execute your analysis in. If you want to do your analysis somme time after the day, I have seen a way to trick ToS into giving you candle by candle reports by executing a strategy that opens and closes a position every candle and copy/paste the strategy results into your spreadsheet.

If you don't like that route, and you feel technically savvy, you can choose to get an Ameritrade Developer Account (it's free) which will allow you to use their API (and python or Java though why anyone would use Java I don't understand) to pull data either live or historical direcly into a program (a small python script could be written to download and export a csv of your data easily enough.

Food for thought
-mashume

 

[ozio72]

I think it depends on the timeframe you wish to execute your analysis in. If you want to do your analysis somme time after the day, I have seen a way to trick ToS into giving you candle by candle reports by executing a strategy that opens and closes a position every candle and copy/paste the strategy results into your spreadsheet.

If you don't like that route, and you feel technically savvy, you can choose to get an Ameritrade Developer Account (it's free) which will allow you to use their API (and python or Java though why anyone would use Java I don't understand) to pull data either live or historical direcly into a program (a small python script could be written to download and export a csv of your data easily enough.

Food for thought
-mashume

Mashume - Thank you for your quick response and suggested approaches. I would like to obtain the dynamic OHLC candle data in real time during the trading day. The Market Watch screen would be the logical place to insert that data since the table is updating in real time and the columns for O, H andL already exist. The issue I believe is that TDA does not wish to provide the data for what ever reason unknown to me. I like the TDA venue and is very helpful for simple trades and analysis. Trading options is another story requiring as much data as possible to make useful analysis of positions and to determine entry and exit points. I understand that candles also provide a good sense of market psychology and would be a valuable asset to have that data exported to Excel in real time as is all of the other data as well. Seems to me that adding the OHLC data would involve a simple coding modification since the data is available as expressed in a candle chart. I may need to find another platform to realize my goal. Thanks again.

 

[halcyonguy]

it's been awhile since i have experimented with exporting data, so i'm a little rusty. i think you can configure excel to pull data on some interval.
you could collect data every x seconds. then use excel to combine the data into the desired timeframe.

the default way RTD works is, it keeps pulling new data, and overwriting previous data.
at the end of this post, is a macro that copies new data to a new cell,

here are some links/info related to RTD


how to export tos data to excel
this will create a collection of formulas in excel, that will pull live data
it will not save it. Each new data overwrites the previous data

TOS
marketwatch > quotes
setup page with symbols and data columns
settings( top right) > export > to microsoft excel
go to an excel sheet, pick a cell , and paste
creates a bunch of rtd( ) formulas, to pull all the data, as it was laid out on marketwatch page.

=RTD( " tos.rtd", , "last", "SPY" )

get last price for stock symbol in cell c1
=RTD( " tos.rtd", , "last", C1 )

i think the Excel formula RTD() will only work on a computer that has TOS installed


https://learn.microsoft.com/en-us/office/troubleshoot/excel/set-up-realtimedata-function


hahn-tech
https://www.hahn-tech.com/thinkorswim-rtd-excel/

thinkorswim RTD excel


https://futures.io/thinkorswim-prog...inkorswim-trading-platform-volumes-price.html

Connecting ThinkOrSwim to Excel
Part 1 of 4
https://www.lockeinyoursuccess.com/connecting-thinkorswim-to-excel-part-1-of-4/
Part 2 of 4
www.lockeinyoursuccess.com/connecting-thinkorswim-to-excel-part-2-of-4
Part 3 of 4
www.lockeinyoursuccess.com/connecting-thinkorswim-to-excel-part-3-of-4
Part 4 of 4
www.lockeinyoursuccess.com/connecting-thinkorswim-to-excel-part-4-of-4-finale

from 1of4
RTD VBA
Building Excel Real-Time Data Components in Visual Basic .NET
https://docs.microsoft.com/en-us/pr...-xp/aa140061(v=office.10)?redirectedfrom=MSDN

may have to adjust excel security settings to get rtd() to work
https://support.microsoft.com/en-us/help/286259/security-settings-and-excel-realtimedata-servers



misc links
--------
https://docs.microsoft.com/en-us/pr...=office.10)#odc_xlrtdfaq_howconfigrtdthrottle
https://docs.microsoft.com/en-us/office/troubleshoot/excel/set-up-realtimedata-function
-----
https://news.cqg.com/blogs/2011/04/adjusting-rtd-interval-throttle-microsoft-excel
https://documentation.softwareag.co...ml#page/um-webhelp/co-throttleinterval_8.html
https://superuser.com/questions/146...-received-through-excel-rtd-and-original-data
-----
https://stackoverflow.com/questions/28397363/pause-rtd-server-in-excel-and-save-worksheet
-----
https://www.codevba.com/excel/RTD.htm#.YUQBTmlOlPw
------
https://forums.aeromir.com/threads/how-to-start-using-rtd-in-excel.1459/page-2
-----



use a macro to copy data to a new cell, to save it

recording spx using tos and rtd function
mhaddadi
[media]

[/media]

each second, this macro copies the price data, from D1, down to a lower cell, in column B.
Cells(m, 2) = Cells(1, 4)
Cells(row, column)
the code would have to be changed to copy more data.

' vba code
' module1
Sub timer()
  ' run a macro every sec
  Application.OnTime Now() + TimeValue("00:00:01"), "main"
End Sub

Sub main()
  ' row counter
  m = m + 1
  ' put time in row m at column 1
  Cells(m, 1) = Now
  ' pull stock data value from D1, put in row m at column 2
  Cells(m, 2) = Cells(1, 4)
  ' call other macro
  timer
End Sub

hal_xls

 

[anarchyjim]

Is there a way via thinkscript (or anything) to write data to a CSV file? i.e. if AddOrder is triggered can that be written to a CSV file with all the other data on the chart?

I know you can do a strategy report but that doesn't show other technical indicators or things like previous close, next open, etc. What I want to do is dump all data on the chart for the day the strategy got triggered, the day before and the day after. For analyzing backtesting this would be helpful.

 

[halcyonguy]

these links might help.

i think you will want to setup the
marketwatch tab with columns of the data you want. then export it to excel, to create the RTD formulas. by default, new data overwrites old data. at the bottom of the 2nd link is a macro to copy data to a new sheet.

Exporting historical data from ThinkorSwim for external analysis
https://usethinkscript.com/threads/...a-from-thinkorswim-for-external-analysis.507/


a post about pulling data into excel
https://usethinkscript.com/threads/...ters-in-my-market-watch-list.7988/#post-76614

 

[mbrandau]

I have a spreadsheet of stocks I am holding and it automatically updates current price. I want to add a column at the end which will calculate the daily close 5 EMA to make sure the closing price stays above the 5 EMA on all my holdings. I don't see anything simple in the Stocks data filters which could do this. Any ideas without getting complicated with tables and VBAs?

 

[mbrandau]

Company Name/Ticker	Period	Cost Price $	Current $	Shares ($1000)	% Inc/Dec	Profit/Loss $
AXOGEN, INC. (XNAS:AXGN)	Nov. 1, 2021 - Nov 30, 2021	$ 15.19	$ 15.87	66	4.48%	$ 44.77
CAPITAL SENIOR LIVING CORPORATION (XNYS:CSU)	Nov. 1, 2021 - Nov 30, 2021	$ 27.58	$ 29.21	36	5.91%	$ 59.10
CARDIFF ONCOLOGY, INC. (XNAS:CRDF)	Nov. 1, 2021 - Nov 30, 2021	$ 5.87	$ 6.11	170	4.09%	$ 40.89
Customers Bancorp, Inc. (XNYS:CUBI)	Nov. 1, 2021 - Nov 30, 2021	$ 53.29	$ 59.19	19	11.07%	$ 110.71
EVANS BANCORP, INC. (XNYS:EVBN)	Nov. 1, 2021 - Nov 30, 2021	$ 39.95	$ 39.85	25	-0.26%	$ (2.63)
LENDINGCLUB CORPORATION (XNYS:LC)	Nov. 1, 2021 - Nov 30, 2021	$ 45.96	$ 47.02	22	2.31%	$ 23.07
LEVEL ONE BANCORP, INC. (XNAS:LEVL)	Nov. 1, 2021 - Nov 30, 2021	$ 31.41	$ 35.83	32	14.07%	$ 140.70
MVB FINANCIAL CORP. (XNAS:MVBF)	Nov. 1, 2021 - Nov 30, 2021	$ 42.69	$ 43.83	23	2.67%	$ 26.70
Olin Corporation (XNYS:OLN)	Nov. 1, 2021 - Nov 30, 2021	$ 56.98	$ 61.03	18	7.11%	$ 71.08
PATRIOT TRANSPORTATION HOLDING, INC. (XNASATI)	Nov. 1, 2021 - Nov 30, 2021	$ 15.33	$ 15.99	65	4.30%	$ 42.99
Totals					5.57%	$ 557.38
S&P 500 INDEX		$ 4,605.38	$ 4,654.23		1.06%

 

[mbrandau]

The Current $ column is the auto update numbers, all other columns are formula based.

 

[2187Nick]

Select the option to customize your watchlist. Search for Custom1 in available items. Edit it so that it is a 5 EMA script. Add it to the watchlist.

Then you will be able to import it into excel

=RTD("tos.rtd", , "CUSTOM1", "AXGN")

 

[mbrandau]

I think you are answering the question as though I would want this in TOS. I am keeping portfolios in Excel 365. So this is my question.
Completely seperate from TOS

 

[MerryDay]

I think you are answering the question as though I would want this in TOS. I am keeping portfolios in Excel 365. So this is my question.
Completely seperate from TOS

yes, @bobmarley solution only works in Excel, completely separate from TOS

https://www.google.com/search?q=rtd...hUKEwjJ-9GOpY70AhXmQvEDHSR1DCoQ4dUDCA4&uact=5

 

[halcyonguy]

I think you are answering the question as though I would want this in TOS. I am keeping portfolios in Excel 365. So this is my question.
Completely seperate from TOS

you say the prices auto update. i think #4 poster was thinking you could add a column in tos that calculates the ema. then export it to excel, then copy that formula to your sheet, so it would pull in the desired ema.

other wise it may be a bit involved to calulate the ema.
https://school.stockcharts.com/doku.php?id=technical_indicators:moving_averages

 

[halcyonguy]

I have a spreadsheet of stocks I am holding and it automatically updates current price. I want to add a column at the end which will calculate the daily close 5 EMA to make sure the closing price stays above the 5 EMA on all my holdings. I don't see anything simple in the Stocks data filters which could do this. Any ideas without getting complicated with tables and VBAs?

this post has a link, to a site that describes the formulas for calculating the ema. it has a excel file you can download.
https://usethinkscript.com/threads/...-not-matching-expected-value.9071/#post-83060

 

[Invesqua]

I have been utilizing the RTD (Real Time Data) function to import data into Excel.

No issues then using the values until I started with financials (10-yr T-Notes, 30 Yr Bonds, etc.) I do not have any issues importing the values into excel.
Here is an example of the function I would be using in Excel =RTD("tos.rtd",,"LAST", "/ZN:XCBT"). The function works and it pulls in the "Last" price for the 10-Yr T-Note. However, where I am struggling is the number format it's providing. For example, 122'075. Within Excel it behaves like text but NONE of the Excel functions to convert text to number or values works. I am always left with the following: #VALUE!. I realize this is as much an Excel related question as it is TOS. But has anybody experienced this previously and did you discover an approach to convert values from the following format 122'075 to 122.075?

Important Note: I can pull other data that is related to CBOT, Wheat, Corn, etc. and the formats are fine. This is only related to Financials.

Thanks for any advice you can provide.

 

[halcyonguy]

I have been utilizing the RTD (Real Time Data) function to import data into Excel.

No issues then using the values until I started with financials (10-yr T-Notes, 30 Yr Bonds, etc.) I do not have any issues importing the values into excel.
Here is an example of the function I would be using in Excel =RTD("tos.rtd",,"LAST", "/ZN:XCBT"). The function works and it pulls in the "Last" price for the 10-Yr T-Note. However, where I am struggling is the number format it's providing. For example, 122'075. Within Excel it behaves like text but NONE of the Excel functions to convert text to number or values works. I am always left with the following: #VALUE!. I realize this is as much an Excel related question as it is TOS. But has anybody experienced this previously and did you discover an approach to convert values from the following format 122'075 to 122.075?

Important Note: I can pull other data that is related to CBOT, Wheat, Corn, etc. and the formats are fine. This is only related to Financials.

Thanks for any advice you can provide.

this will convert a text number to a number

excel

control ~ ( control tilde)
show formulas

paste this into a1
below the slashes
////////////////////////////////

        =RTD("tos.rtd",,"LAST", "/ZN:XCBT")
=RTD("tos.rtd",,"LAST", "/ZN:XCBT")        price as text,  RTD( )
=LEN(A2)        length data text
=SEARCH("'",A2,1)        decimal location
=A3-A4        right char len
      
=LEFT(A2,A4-1)        left digits
=MID(A2,A4+1,A5)        right digits
.        decimal point
      
=VALUE(A7&A9&A8)        price converted to number
1        some number
=A11*A12        number x price

then press control ~ again , to see values , not formulas