Getting twsdde.xls to work without errors with latest TWS and API

For years, the minimum steps to get Excel setup to receive data and send orders via DDE  with IB were the following.

  1. Install TWS
  2. Install the API
  3. Open TWS and login
  4. From the TWS menu, Select File>Global Configuration>API>
  5. Place a check next to “Enable DDE clients”
  6. open C:\TWS API\samples\excel\twsdde.xls
  7. Select the “Tickers” tab.
  8. Enter User Name in B5 (see below if using edemo)
  9. Select A13 (or any symbol’s row in column A).
  10. Press the “Request Market Data” button.

Currently there are two potential reasons why the above will no longer work. In step 5 above, you will receive an error that says “The ddedll.dll file required for Excel integration is either missing or out of date.”

Screen Shot 2016-01-19 at 1.36.07 PM

The error occurs when the 64 bit version of TWS is installed. DDE is only supported in the 32 bit version. The current download page that IB has in place has an unusual, non-intuitive way to find the link to the 32 bit version.

  1. From the TWS download page, select “TWS Latest” or “Offline TWS Latest”. The “Download” button will appear and the link will be for the 64 bit version that you do not want if using dde.
  2. Choose “Download for Other Operating Systems” (a list appears).
  3. Choose “Windows 32 bit”. The link available from the “Download Button” will change to 32 bit. If you look closely, you’ll also see the small text under the Download button change to “Windows: 32 bit”.

 

tws32

Alternatively, you can access the 32 bit version from the following link.

https://download2.interactivebrokers.com/installers/tws/latest-standalone/tws-latest-standalone-windows-x86.exe

The information below only applies to the edemo account. (The edemo account is provided to the public. It does not require an account unlike a papertrading account. The data is not real, however it’s useful for testing functionality of Excel based programs.)

The second thing that has changed and that is particularly confusing for users who do not have a funded account with a papertrading account with live data is that the edemo account no longer appears to work even when you run the the 32 bit version of TWS and have followed all the the 10 steps at the beginning of this post. Instead, when you use the edemo account, you get an error that says “Remote data not accessible. To access this data. Excel needs to start another application…..”Start application ‘SEDEMO.EXE?”

 

Screen Shot 2016-01-19 at 2.42.33 PM

In a recent update to TWS. It appears that the username “edemo” silently has a number appended like “edemo21”, “edemo22”. This modified username is not obviously visible to the user. Since using twsdde.xls (or my own ATS.xls) requires that you enter the username, it will always fail with the “Remote data not accessible” error when using “edemo”.

The only way, I currently know to find out the modified edemo username that you’ll need to enter into twsdde.xls (or ats.xls on the settings page) is to first login to TWS using edemo (edemo, demouser) and then once connected, disconnect your computer from the internet until you see the following disconnection message appear from TWS. In this case the message says “Logging in edemo161”. Now we know “edemo161” is the correct user name to enter into twsDde.xls or any dde based excel program that accesses IB.

Screen Shot 2016-01-19 at 2.53.21 PM

 

 

 

 

 

Excel Benchmark 2011: An Excel Speed Test (with trading functions)

Live Analysis of Benchmark Results

Of 4794 Benchmark results, currently the Excel version with the highest *average Total Score of 58.59 is Excel 2013. Excel 2010 has an average Total Score of 49.57. Excel 2002 has an average Total Score of 47.46. Excel 2003 has an average Total Score of 42.91 and the worst average Total Score is Excel 2007 at 23.74.

Of the top 30 scores, 80% of the processors brand name is Intel. Followed by AMD which is the processor in 20% of the top 30 results. The exact processor with the current top score of 132.6614 is Intel(R) Core(TM) i5-2500K CPU @ 3.30GHz. It's likely that this processor is overclocked beyond what is shown on the benchmark results page and is not necessarily the top performing processor at default settings.

Of the six tests used to determine Total score, Test 3 shows the most variance between Excel Versions. For example the best score for test 3 in Excel 2003 is 56785 while the best test 3 score for 2010 is 24137896 so for test three, Excel 2003 performs 0.24% better when only the best scores are considered. Test 3 measures how many ticks can be displayed on a stock chart within a 30 second time period. The other tests composing Total Score involve more intensive calculation tests than this charting display test. Although this difference is large, Test 3 should only be considered over Total Score for the most charting intensive Excel programs where VBA and/or formula based calculations are relatively minimal. For most Trading applications Total Score which is a composite of the 6 tests is the most useful score to use for evaluating Excel versions/Processors/OS etc.

Of the 4794 current results 775 are below 0. I would not consider any system scoring below 0 adequate for running an excel based trading system. A modern system scoring below 0 should be checked for unnecessary startup programs, spyware, viruses etc.

*Scores below 0 are set to zero for purposes of calculating average total score.

BenchMark_ET.xls measures Excel performance with 6 trading related Excel/VBA tests. Once the benchmark is complete, your results can be submitted to the Database (just press “Submit”). All submitted results are available here (all results are sortable). The default view is most recently submitted result topmost (so your result should be on top until you sort). System Information is listed along with each test result (processor name, OS, Clock Speed, Cores, Excel Version, File type). A composite Scoring system is in place, but each individual test result is also listed in the results database. All results can be sorted in ascending/descending order by clicking each result’s title. For best results disable autosave and background error checking in Excel options.

Instructions:

  1. Download Benchmark_ET.xls.
  2. Open Benchmark_ET.xls and press the button “Start Benchmark”
  3. (optional) Submit and view your results.

Benchmark for Excel by Exceltrader

Below is an explantion of each test:

Test 1: Data Build Time

Measures the time it takes VBA to create 5 columns * 65535 rows of random tick data.

Test 2: MACD VBA Calculation

Measures the time it takes VBA to calculate the MACD indicator for the 5 columns * 65535 rows of data created in test one. (The VBA code for the MACD calculation is here.)

Test 3: Dispayed Ticks (30 Sec)

Measures the number of ticks (price changes) than can be displayed in 30 seconds. In my original benchmark this was the only type of test. The results varied so greatly that newer versions of excel took hours to complete the benchmark while older versions took just minutes making the benchmark annoying to run. By limiting this test to 30 seconds the total time to run the benchmark will be similar among all systems tested.

Test 4: 63000 ticks converted to OHLC

After 63000 ticks are created, this test converts the tick data to OHLC data. The result is measured in time.

Test 5: Live formula Test

This test begins with with 5000 bars of OHLC and a series of formula based indicators. The last price changes in E5000 as rapidly as possible but waits for all formulas to calculate between each price change.  The benchmark result is the number of price changes that can be completed with formula recalculations within 30 seconds.  Below is the list of indicators calculated with live formulas in this test.

  1. SMA (100,200,300)
  2. RSS
  3. RSI
  4. Fisher Transform of RSI

Test 6: Live formula Test

This test is identical to test 5 except that all formulas also refer to and are dependent on the changing cell, E5000. Test 6  compared to Test 5  is meant to test the “smart recalculation engine” promoted by Microsoft.  More info can be found in this Microsoft article under the section titles “Understanding Calculation Methods in Excel”.

Scoring:

A composite scoring system is in place and the result is listed in column two on the results data base page and also at the top of the benchmark results form upon completion. With the exception of test 3 all tests are counted equally. Test 3, which tests displayed ticks on a stock chart is counted 3 times because for trading it’s an important result to consider. To establish the composite score 20 tests were run on various systems and excel versions. The best and worst scores for each test were used to establish the benchmark score. The best scores from the initial tests resulted in a score of 100 and the worst scores resulted in a score of 0 therefore scores greater than 100 and below 0 are possible.  On the results page idle processor speed is shown and overclocked systems may not show the true processor speed. If you see two systems that look the same with significantly different scores, the higher score is likely from an overclocked system. Thanks for trying my Excel benchmark and submitting your system’s result!

 

Bond Data Converter

U.S. Government Bonds and Notes quotes are not easily understood by excel. A quote’s fractional part is often separated by a colon or space and the denominator is assumed. For example 100:02 is the form given by the Wall Street Journal. From other sources it might appear as 100 02. In this quote the 02 is the numerator and the denominator (32) is assumed. To put back in general number form that excel can read the math is 100 + 02/32 which is 100.0625. The below code that I wrote is from
Bondconvert.xls. The code will convert all the data in columns B-E to number form if it has a space between the the two parts. The code can be adapted easily.
For example: to convert data with a colon instead of a space you would change the line y = Split(x," ") to y = Split(x,":")

In addition to the code based conversion, the function can also be used with live data. For example the function can be used in a formula like Bconver("120 01.5"). In that case "120 01.5" could be replaced with a dde link.

Option Explicit

Function Bconver(x As Variant)
Dim y As Variant
y = Split(x, " ")
If UBound(y) = 1 Then
Bconver = y(0) + y(1) / 32
Else
Bconver = x
End If
End Function

Sub bondconvert()
Dim wsET As Worksheet
Dim lr As Long
Dim r As Range
Dim x As Variant
'Set wsET = ThisWorkbook.Worksheets("Data")
Set wsET = ThisWorkbook.ActiveSheet
With wsET
lr = .Cells(Rows.Count, "A").End(xlUp).Row
For Each r In .Range("B2:E" & lr)
r.Value = Bconver(r.Value)
Next r
End With
End Sub

'www.Exceltrader.net, Copyright © 2011 Excel Trader - All Rights Reserved

Earnings 2

****NOTE: A new version is now available here. This version is no longer maintained.

A version that downloads from briefing.com.