SteadyOptions is an options trading forum where you can find solutions from top options traders. Join Us!

We’ve all been there… researching options strategies and unable to find the answers we’re looking for. SteadyOptions has your solution.

Christof+

Free RV Real-Time Tool (with Chartaffair data)

Recommended Posts

For anyone interested here is a lightweight tool for plotting real-time and historical intraday RV values.

 

image.png

 

 

image.png

image.png

 

 

 

 

Download: bit.ly/rt-rv

Short video on major features: bit.ly/rt-rv-v

 

 

This tool is provided for free for your convenience.

It is excel based and uses Interactive Brokers API for real-time data (follow instructions on download page).

 

Feel free to download and use it. It works completely independent of any chartaffair subscription.

However, if you happen to have a valid chartaffair premium subscription it will additionally produce up-to-date earnings data through the chartaffair API. The test key included will work for a few days from now.


The VBA code inside is 100% open and accessible and anyone wanting to is invited to modify and repost it (f.e. to add more brokers).

 

 

 

Edited by Christof+

Share this post


Link to post
Share on other sites

@Christof+, I took a quick look at your free tool - nice idea and implementation! Although TWS API is quite slow to return data history.

Here are a few changes to add support for 64-bit Excel (I tried this on Excel 2019 64-bit):

1. Change GetTimeZoneInformation() return type to Long:

Quote

#If Win64 = 1 And VBA7 = 1 Then
    Private Declare PtrSafe Sub GetSystemTime Lib "kernel32" (ByRef lpSystemTime As SYSTEMTIME)
    Private Declare PtrSafe Function GetTimeZoneInformation Lib "kernel32" (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
 

2. Change variable types to hold GetTickCount() value to LongPtr in TakeNap():

Quote

Public Sub TakeNap(ByRef milliSeconds)
    
    Dim StartTick As LongPtr
    Dim CurrTick As LongPtr
    Dim EndTick As LongPtr
 

3. I also had to type 127.0.0.1 to the Host field on General page. With empty host the ActiveX control did not want to connect to TWS. So I suggest setting this field to 127.0.0.1 by default.

4. Plus I disabled the annoying message that ChartAffair Key is invalid, as it appreared too often.

I attached Excel sheet with my modifications - probably would be helpful to someone.

 

Chartaffair Real-Time RV Tool.xlsm

Share this post


Link to post
Share on other sites

@Christof+, yes, old little programming trick with LongPtr. :) My version should work on 32-bit versions of Excel too. All my modifications are backward compatible with 32-bit builds, and I didn't change output file format (so it should work on pre-Excel 2019 versions). Probably it's worth checking - if this sheet works fine with your Excel 32-bit version, you can keep only one version on the website.

Share this post


Link to post
Share on other sites

@Christof+, I found a bug in your "Free RV Real-Time Tool". I noticed that when I request RV data for calendars, the tools sometimes does not show the most recent data. For example, on 13.02.2020 I ask to plot calendar RV for NVDA, and the tool only shows data from 06.02.2020 and before that on the charts.

It turned out the problem is how dynamic named ranges Cal_* are defined:

=Calendar!$AS$6:INDEX(Calendar!$AS$6:$AS$293, COUNTA(Calendar!$AS$6:$AS$293))

Index 293 is not big enough to hold all the historical data that IB returns (especially for intervals like "5 mins => 1 week"). After I changed this index from 293 to 2000 everywhere in the Name Manager, the charts started to plot correctly:

=Calendar!$AS$6:INDEX(Calendar!$AS$6:$AS$2000, COUNTA(Calendar!$AS$6:$AS$2000))

I'm not providing a fixed Excel Sheet this time as I made some other test modifications to the sheet that other users may not need. But I'm reporting the bug here.

The same bug should also be present on Straddle page with Stdl_* dynamic named ranges, but I did not have a change to test it.

Share this post


Link to post
Share on other sites

@Drew_Brosenhaus, on overall, IB TWS is very slow to return historical data. Looks like their architecture had been designed in 1980 and did not change since that time. So when generating Straddle/Calendar RV charts it is ok to wait a minute or sometimes even more while TWS returns all required data.

As for Watchlist, I've just checked - works pretty fast. When I type a new ticker into Symbol column, the tool automatically updates expiry, RV and other info in the watchlist. Probably you tried to test this on weekends, and IB does some server maintenance on Saturdays, so TWS does not work correctly till Monday. Try again in trading hours. Also try to type a different ticker into Symbol column.

Share this post


Link to post
Share on other sites

@Drew_Brosenhaus My experience is the same as @Stanislav's. You can compare the time needed for IB API to return historical data to the time it takes when you request a intra-day chart for an options combo in TWS. That can well take even 5 min until the chart is filled. Looks like API connections and charts get their data via the same mechanics under the hood.

Yesterday's RV data directly comes from my server and should be returned very fast. It additionally gets cached in the tool.

Share this post


Link to post
Share on other sites

@Christof+ @Stanislav Just got the error message when re-checking this morning

"The ddedll.dll file required for Excel integration
is either missing or out of date.

The dll can be refreshed with the most up-to-date version can by
downloading and installing the API Software from our web site.

Please close the application and restart it after the API components have been updated."

Odd because I initially got this yesterday despite downloading the API per instructions, then re-downloading, which seemed to work but I must be missing something. Not super savvy with these things, but I can go through process again and see what happened. 

 

Share this post


Link to post
Share on other sites
1 hour ago, Stanislav said:

@Drew_Brosenhaus, see here:

https://ibkr.info/node/2155

Probably your Excel and TWS architecture does not match. For example, this can be if you are using 64-bit TWS and 32-bit Excel or vice versa.

So Installed proper version, still stuck on requesting underlying price when I refresh.. Went through entire checklist again and everything looks good. Any other suggestions? 

Share this post


Link to post
Share on other sites
2 hours ago, Drew_Brosenhaus said:

So Installed proper version, still stuck on requesting underlying price when I refresh.. Went

through entire checklist again and everything looks good. Any other suggestions? 

Are you still observing an error "The ddedll.dll file required for Excel integration is either missing or out of date"? I think your chances of receiving a meaningful answers are better if you prove slightly more info on what is going on on your system.

 

Here are some general suggestions:

 

- Does you TWS arch matches Excel architecture? I.e. TWS 32-bit and Excel is 32-bit. Check in Task Manager that excel.exe and javaw.exe for TWS are both 32-bit processes (or both 64-bit processes);

- Did you install latest version of TWS API? Available here: https://interactivebrokers.github.io/#

- Ensure that you enabled "Enable Active X and Socket Clients" in Global Configuration/API/Settings;

- Check that "Socket port" in TWS settings matches port specified in Excel RV Tool;

- Enter Host: 127.0.0.1 in Excel RV Tool (with this field empty the tool did not work on my machine);

- Check that C:\Windows\ddedll.dl file exists (you can post here this file, so we can check what architecture this file is for);

 

Finally, if nothing helps, look at "ActiveX Sample Spreadsheet" that comes with TWS API. Usually located here: C:\TWS API\samples\Excel\TwsActiveX.xls. Ensure it works ok. It may also worth to take a look at TwsDde.xls and/or TwsRtdServer.xls samples.

 

Probably there are some issues on 64-bit Windows with 32-bit TWS/32-bit Excel & ddedll.dll. On my system both Excel and TWS are 64-bit applications. Everything works fine, despite the fact that C:\Windows\ddedll.dll file is for 32-bit architecture - most likely this file is not used at all when TWS/Excel are 64-bit. That is proved by the following remark from IBKR article I quoted above:

Quote

 

Other Items: 

Please be aware that there are some programs that can effect the legacy DDE Client connections, for example: Chrome, VLC Player, Adobe Creative Cloud or Microsoft One Drive. In case you are using these programs, please keep them closed while you are using the Excel DDE workbook.
The newer DDE version uses a Java socket based on 64-bit, and should no longer have any conflicting software.

 

So, your best bet is to switch to 64-bit Excel and 64-bit TWS. This configuration is proven to work.

 

You can also try to close all other applications such as Chrome, VLC Player, Adobe Creative Cloud or Microsoft One Drive, as suggested in the quote above.

Edited by Stanislav

Share this post


Link to post
Share on other sites
2 hours ago, Stanislav said:

Are you still observing an error "The ddedll.dll file required for Excel integration is either missing or out of date"? I think your chances of receiving a meaningful answers are better if you prove slightly more info on what is going on on your system.

 

Here are some general suggestions:

 

- Does you TWS arch matches Excel architecture? I.e. TWS 32-bit and Excel is 32-bit. Check in Task Manager that excel.exe and javaw.exe for TWS are both 32-bit processes (or both 64-bit processes);

- Did you install latest version of TWS API? Available here: https://interactivebrokers.github.io/#

- Ensure that you enabled "Enable Active X and Socket Clients" in Global Configuration/API/Settings;

- Check that "Socket port" in TWS settings matches port specified in Excel RV Tool;

- Enter Host: 127.0.0.1 in Excel RV Tool (with this field empty the tool did not work on my machine);

- Check that C:\Windows\ddedll.dl file exists (you can post here this file, so we can check what architecture this file is for);

 

Finally, if nothing helps, look at "ActiveX Sample Spreadsheet" that comes with TWS API. Usually located here: C:\TWS API\samples\Excel\TwsActiveX.xls. Ensure it works ok. It may also worth to take a look at TwsDde.xls and/or TwsRtdServer.xls samples.

 

Probably there are some issues on 64-bit Windows with 32-bit TWS/32-bit Excel & ddedll.dll. On my system both Excel and TWS are 64-bit applications. Everything works fine, despite the fact that C:\Windows\ddedll.dll file is for 32-bit architecture - most likely this file is not used at all when TWS/Excel are 64-bit. That is proved by the following remark from IBKR article I quoted above:

So, your best bet is to switch to 64-bit Excel and 64-bit TWS. This configuration is proven to work.

 

You can also try to close all other applications such as Chrome, VLC Player, Adobe Creative Cloud or Microsoft One Drive, as suggested in the quote above.

Thanks, @Stanislav --Sorry for choppy response earlier. So specifically, yes, after realizing I have 32-bit excel installed on my home machine, I uninstalled 64-bit TWS and installed the 32-bit version. 

I am no longer receiving the .dll file error, that seems to have been solved. 

I installed the latest version of TWS API, even re-did it just to be sure. 

Followed the checklist to ensure I enabled Active X & stock clients, Port is correct.

***Host I WILL need to check when I get home. I don't recall if it was empty. I believe it was auto populated when I opened the file. I will also need to check to make sure the C:\Windows\ddedll.dl file exists -- Perhaps one of these are the issue, but I know the others were taken care of. 

Greatly appreciate the help, thank you. 

Share this post


Link to post
Share on other sites

@Stanislav strange.. I added the host, which was blank. Tried the samples and they're functioning. Still cant get it to work. 

 

I tried to open the ddedll.dll file but it comes out like a bunch of squares. My excel was 32 bit when loaded on the computer. I'll see if I can get it to 64 to check if that works. Appreciate all your help on this. 

ddedll.dll

Edited by Drew_Brosenhaus

Share this post


Link to post
Share on other sites

@Drew_Brosenhaus, try also to generate RV chart of some straddle or calendar in ChartAffair RV Tool from Straddle/Calendar pages. See if this works. Be prepared to wait a minute or two while IB returns all historical data.

 

Also, look at Log page in ChartAffair RV Tool. Log page displays all debug messages that TWS returns when code communicates with TWS via API. This may give some clues of what is wrong.

 

Another idea, as @Christof+ said RV Tool gets yesterdays RV data directly from chartaffairs.com web site - maybe you have some kind of firewall which blocks connections to chartaffairs.com from excel and this makes tool stuck in the middle of the process?

Share this post


Link to post
Share on other sites
6 hours ago, Stanislav said:

@Drew_Brosenhaus, try also to generate RV chart of some straddle or calendar in ChartAffair RV Tool from Straddle/Calendar pages. See if this works. Be prepared to wait a minute or two while IB returns all historical data.

 

Also, look at Log page in ChartAffair RV Tool. Log page displays all debug messages that TWS returns when code communicates with TWS via API. This may give some clues of what is wrong.

 

Another idea, as @Christof+ said RV Tool gets yesterdays RV data directly from chartaffairs.com web site - maybe you have some kind of firewall which blocks connections to chartaffairs.com from excel and this makes tool stuck in the middle of the process?

The odd thing is I can go into the straddle tab, as an example, change the ticker, and will return new strikes & EA. So some data is coming through, but charts are not updating. On watchlist I get the text for requesting underlying price, but RV, put, call etc don't refill with anything. 

On the log page, I get a series of messages saying Market data farm connection is OK: "xxxxx" (X being different for each line)

Share this post


Link to post
Share on other sites
2 hours ago, Drew_Brosenhaus said:

The odd thing is I can go into the straddle tab, as an example, change the ticker, and will return new strikes & EA. So some data is coming through, but charts are not updating. On watchlist I get the text for requesting underlying price, but RV, put, call etc don't refill with anything. 

On the log page, I get a series of messages saying Market data farm connection is OK: "xxxxx" (X being different for each line)

"change the ticker, and will return new strikes & EA" // - do you mean the tool returns EA and a list of "Available Expiries"? ChartAffairs RV Tools asks TWS via TWS API for the list of "Available Expiries". So this part seems to be working ok.

 

Can you please posts screenshots of what you see in Excel on a Straddle page after you change Symbol and hit Run button (and wait 1-2 minutes):

 

1. Screenshot of the Straddle page config area, note that tool displays current status in red (on this screenshot "Symbol ok" - this is the main diagnostics tool which reveals RV Tool's internal state):

image.png

 

2. Screenshot of the RV charts.

image.png

 

3. Screenshot of the AD4:AVxx cells:

image.png

 

4. And a screenshot of the Log page.

 

This can help to diagnose the issue.

 

Edited by Stanislav

Share this post


Link to post
Share on other sites
55 minutes ago, Stanislav said:

"change the ticker, and will return new strikes & EA" // - do you mean the tool returns EA and a list of "Available Expiries"? ChartAffairs RV Tools asks TWS via TWS API for the list of "Available Expiries". So this part seems to be working ok.

 

Can you please posts screenshots of what you see in Excel on a Straddle page after you change Symbol and hit Run button (and wait 1-2 minutes):

 

1. Screenshot of the Straddle page config area, note that tool displays current status in red (on this screenshot "Symbol ok" - this is the main diagnostics tool which reveals RV Tool's internal state):

image.png

 

2. Screenshot of the RV charts.

image.png

 

3. Screenshot of the AD4:AVxx cells:

image.png

 

4. And a screenshot of the Log page.

 

This can help to diagnose the issue.

 

image.pngimage.pngimage.pngimage.png

 

 

So I entered BABA, symbol ok and returns expiration dates. Thank you again @Stanislav

Share this post


Link to post
Share on other sites

Did you hit "Run" button? "Symbol ok" message is printed after you type a new Symbol, but after that you have to press Run button for the tool to actually start building RV charts.

image.png

 

Also, from the Log page the message "Requested market data is not subscribed. Delayed market data is available." reveals what might be a problem. You need to subscribe to OPRA data for options, and to some bundle to get realtime stock quotes.

As far as I remember TWS API needs special flags to work with delayed data, and chances are high ChartAffair RV Tool currently does not work with delayed data subscriptions.

Share this post


Link to post
Share on other sites
Just now, Stanislav said:

Did you hit "Run" button? "Symbol ok" message is printed after you type a new Symbol, but after that you have to press Run button for the tool to actually start building RV charts.

image.png

 

Also, from the Log page the message "Requested market data is not subscribed. Delayed market data is available." reveals what might be a problem. You need to subscribe to OPRA data for options, and to some bundle to get realtime stock quotes.

As far as I remember TWS API needs special flags to work with delayed data, and chances are high ChartAffair RV Tool currently does not work with delayed data subscriptions.

When I hit run I get the "waiting for underlying symbols" 

Very bonehead move by me. I don't do majority of my trading on IB and the sub issue is probably what's going on here. 

Share this post


Link to post
Share on other sites
11 minutes ago, Drew_Brosenhaus said:

Appears it may be an issue with my subscriptions - which are you subscribed to on IB? I'll make sure it's set up correctly 

Yep. That's correct. You need the following two subscriptions:

 

1. US Equity and Options Add-On Streaming Bundle

2. US Securities Snapshot and Futures Value Bundle

 

NOTE: "US Securities Snapshot and Futures Value Bundle" fee is waived if monthly commissions reach 30 USD.

 

image.png

Edited by Stanislav

Share this post


Link to post
Share on other sites

Here is another ChartAffair RV Tool update. I fixed a bug similar to the previous one. When building straddle RV chart I noticed there are some artifacts in the end of the chart. Turned out DisplayFrontendData() does not properly clear front end data on the sheet before populating it with new values - it only clears as much as current RvData occupies. However, previous stock RV data can occupy larger range, so some values remain from previous RV chart and that messes up things.

 

To fix this I added the following line in DisplayFrontendData() in straddle and calendar code (shown in bold):

Quote

    ThisWorkbook.Worksheets(STR_SHEET_STRADDLE).Range(rg, rg.Offset(UBound(RvData, 1) + 1)).Resize(, 5000).ClearContents
    ThisWorkbook.Worksheets(STR_SHEET_STRADDLE).Range(rg, rg.Offset(UBound(RvData, 1) + 1)).Resize(, UBound(RvData, 2) + 1).ClearContents
 

Quote

    ThisWorkbook.Worksheets(STR_SHEET_CALENDAR).Range(rg, rg.End(xlDown)).Resize(, 5000).ClearContents
    ThisWorkbook.Worksheets(STR_SHEET_CALENDAR).Range(rg, rg.End(xlDown)).Resize(, UBound(RvData, 2) + 1).ClearContents

 

Chartaffair Real-Time RV Tool.xlsm

Edited by Stanislav

Share this post


Link to post
Share on other sites

Just to make sure:

Does this only work with a market data subscription for IB? Did not find anything related. The log says I did not subscribe to the market data package and any data will be delayed. Does it only work during market hours? The file is stuck in "requesting underlying price", but the TwsActiveXLS also does not fetch any data (same message).

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account. It's easy and free!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now

  • Similar Content

    • By Christof+
      Most members will know www.art-of.trading, an open website I run with the purpose of easy plotting RV charts.
      Now I have some bad news to convey. But there is some good news also.
       
      First the bad news:
      I will discontinue www.art-of.trading. After running it and offering its content for free for more than a year now I will not maintain the webservice longer in its current form. Daily updates will continue for now but the site will not be accessible anymore within some short period of time.

      Now here is the good news:
      There will be a replacement - a good one. Taking the essence of numerous discussions here on Steady Options and my personal take-aways and learnings from running art-of.trading I am up to offer an entirely new service for providing tools for trading the Steady Options way (in the same breath I should add a big thank you for all remarks and positive feedback I have gotten over the entire period. That is what made me take that route. On top I see that Steady Options continues to be a unique community on the web for trading options, with very good people and quality content - reason enough to stick around).
       
      I invite you to take a look at https://www.chartaffair.com
       
      Currently the site is working but open only to beta testers and not 'officially' released yet. Am still looking for some more beta testers. If you want to have early access I invite you to PM me for a free invitation code. Beta testers will get free access for some time once the site launches officially.
       
      Technically chartaffair.com is a more professional approach to running a webservice. And I have completely redesigned its code base. It uses a new data architecture, specifically targeted at handling and serving from large amounts of data. This together with fast hosting hardware and the use of new web technologies allows for two things:
       
      1) Greatly increase available information
      You will find much more tables and graphs for each symbol with all information needed for trading the SO way: Historical implied move vs. actual move (as graphs and tables), credit needed for hedging straddle decay, actual performance of straddles around earnings. But also basic stuff like the next dividend ex-date or how long before the actual earnings announcement the announcement date gets confirmed (more details on specific features in later posts). Also, you will find practically any symbol now which comes at least with some traded options volume.
       
      2) All information for a symbol aggregated on one single page
      Having all information in one place elminates the need to jump back and forth between different pages and websites, having to reenter the same symbol again and again (which I understood is an issue). A side bar allows for easy and fast navigation up and down on the page.
       
      All features came out of discussions and from my own experiences while trading. I believe they will be helpful. I will introduce some in greater detail in a couple of follow-up posts in this thread. On top of that there are a couple more pages to be added to this site. They are in planning and partly already in implementation. They will be added in the course of the next weeks/months.
       
      Chartaffair will be a paid service after the beta phase (it is not possible elsewise. But it will be worth it.) Now, if you found art-of.trading helpful over the course of last year, I invite you to sign up to chartaffair.com when the beta phase is completed. You will find it even more helpful.
       
  • Recently Browsing   0 members

    No registered users viewing this page.