Free RV Real-Time Tool (with Chartaffair data)

[Christof+]

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

 

 

 

 

 

 

 

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 December 4, 2019 by Christof+

[Stanislav]

@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

[Christof+]

Nice @Stanislav !
 

Did actually not think of the LongPtr type (there indeed were a couple of data type issues about which users wrote me about and which I was unable to reproduce).

I will add your version as the separate 64-bit version to the download page.

[Stanislav]

@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.

[Stanislav]

@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.

[Christof+]

Very nice, @Stanislav great you digged that far into it!

Will add the fix to both download versions when I get a chance!

[Stanislav]

I found some time to merge my fixes into Excel 2019 x64 compatible version of the spreadsheet that I released in the post above.

Here is the fixed version with "most recent data on RV chart fix" included.

Chartaffair Real-Time RV Tool.xlsm

[Christof+]

Thank you @Stanislav added this version to the download page

[Drew_Brosenhaus]

@Christof+ Awesome tool. Thank you. 

 

I seem to have everything up and running. Request current time function works. When I hit update once on watchlist I get the requesting underlying price status, but hasn't done anything in a few minutes.. Typical? or do I have an issue?

[Stanislav]

@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.

[Christof+]

@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.

[Drew_Brosenhaus]

@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. 

 

[Stanislav]

@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.

[Drew_Brosenhaus]

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.

You got it.. Windows is 64 bit, but my excel 32 bit. Just checked....

[Drew_Brosenhaus]

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? 

[Stanislav]

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 February 24, 2020 by Stanislav

[Drew_Brosenhaus]

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. 

[Drew_Brosenhaus]

@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 February 25, 2020 by Drew_Brosenhaus

[Stanislav]

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

[Drew_Brosenhaus]

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)

[Stanislav]

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):

 

2. Screenshot of the RV charts.

 

3. Screenshot of the AD4:AVxx cells:

 

4. And a screenshot of the Log page.

 

This can help to diagnose the issue.

 

Edited February 25, 2020 by Stanislav

[Drew_Brosenhaus]

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):

 

2. Screenshot of the RV charts.

 

3. Screenshot of the AD4:AVxx cells:

 

4. And a screenshot of the Log page.

 

This can help to diagnose the issue.

 

 

 

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

[Drew_Brosenhaus]

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 

[Stanislav]

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.

 

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.

[Drew_Brosenhaus]

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.

 

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. 

[Stanislav]

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.

 

Edited February 25, 2020 by Stanislav

[Stanislav]

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 March 9, 2020 by Stanislav

[Christof+]

Great @Stanislav much appreciated!

[Daniel S]

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).

[Stanislav]

@Daniel S, yes you need real-time market data subscriptions for RV Tool to work. RV Tool does not work with delayed data. See above my post that explains which subscriptions you need.

 

Edited March 14, 2020 by Stanislav

[PacMan]

Hi @Christof+

Is still the tool available for everybody? Do I need a Chartaffair subscription?

Thanks

[Christof+]

@PacMan The RV-Tool is free to download and use. No chartaffair subscription is required. But you need a data subscription with IB in order to pull real time market data.

[juanchy]

Hi Christof and all, I found this great tool but after installation I got the following message when I open the macro file. I wonder if anyone knows how to solve it. Many thanks

[NikTam]

@Christof+  I'm interested in your app -- can you tell me if the backtesting is similar to Trade Machine?  Or how does it differ?  Thanks!