Earnings_2016.xls

Description:

earnings_2016.xls is a newer/better version of the older earnings.xls Excel file that retrieves earnings dates from various sources (requires Windows).

Usage:

  1. Download earnings_2016.xls (File Size:54.0 KiB, Total downloads: 93, Last Updated: January 25, 2016)
  2. Enter Stock Symbols in Column A
  3. Press “Get Earnings”

earnings2016

Notes:
One of the most downloaded files on exceltrader.net is the original version of earnings.xls. The earnings dates are retrieved from various sites that often change. After a source made a change, it was common for earnings.xls to stop working until I had time to update it.

earnings_2016.xls (File Size:54.0 KiB, Total downloads: 93, Last Updated: January 25, 2016) is a complete do over with an attempt to make it very easy for a VBA beginner to make a change (or customize) and get things working again without having to wait for me to do it. In addition, it will be faster for me to update as needed.

As an example, on any page that contains an Earnings Date, view the html source and find a unique string near the date. On line 6 below, a value that is currently working on yahoo is “Earnings Date:”. The next string we’ll use is yfnc_tabledata1″> between the first string and the date and that’s all we need to find the date.

Screen Shot 2016-01-24 at 7.55.50 PM

If the site changes the “Earnings Date:” to something like “Earn Date:”, then the code will stop working. Most likely it can be fixed simply by changing line six below in earningsmodule.bas. (Most likely the classes won’t need to be modified.)

The exported module and classes are available on github. If you spend time making additions or fixes that you would like to share, consider a pull request.

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

 

 

 

 

 

A small program to assist IB login for users with Security cards.

IB now provides account holders with a plastic security card that has 224 numbers (1-224)  listed next to each number’s corresponding three digit unique key. To login, the user must look up two numbers provided by IB at login and then find and type in the corresponding two keys. Many find the process cumbersome and opt out of being required to use the security device. Opting out requires the account holder to sign forms giving up certain rights and is not a great choice considering that IB for some reason limits password maximum length to 8 characters.

 

 

LogFast_et is a small program to assist the cumbersome process of logging in. Once the program is set up, The user opens the small program, enters the 2 numbers displayed by IB and the resulting code is copied automatically to the clipboard so that it can be pasted in  IB’s login area to complete the login.

Logfast (641.5 KiB, 2679 downloads)

Initial Setup: Download and install the program. To setup the program, there is a form in place available from the settings menu to enter the values from the security card. The setup should take most people around 20 minutes to enter all 224 entries. Fortunately you’ll only have to do it once.

After you install the program you will have two files in the program’s directory. One is the executable and the other is the .sdf file which is the database. The keys that you enter from your card are kept on your computer in a local database (sdf) that is encrypted and password protected.  The program retains your encrypted password in it’s settings so you do not have to enter it each time the program opens. You can change the password from the Settings menu. This means that no one can view your database of keys directly unless they know your password. On the other hand anyone who opens the program can view each key by manually entering three digit numbers. Upon install it is recommended to change the default password (which is “password” and is auto filled as the old password) to a different password that you can remember.

Below is a short video showing the program in action.  Please use the comment form below for any questions, comments, feature requests.

Fixing Run-Time error ‘6’ Overflow when using Excel twsdde.xls to send orders to IB

Twsdde.xls (the sample excel file provided by Interactive Brokers) contains a procedure that creates order ID’s using a calculation based on the current date and time. The variable “ongoingID” is defined as type Long. On 8/26/2012 the calculation began producing a value higher than the maximum value for a Long in Excel which is 2147483647 [wiki].

To solve this error I currently recommend the following steps:

From TWS (Trader Workstation) menu:

  1. Navigate “edit”>global configuration>API
  2. Press the “Reset Order ID and ID sequence” button

 

In any workbook that you use such as Twsdde.xls or any custom Excel/VBA workbook used to send orders to IB:

  1. Open the workbook and press Alt+F11 to open the vba editor
  2. Press CTRL+F (or use menu) to open the Find (search) box. Search for and change “Const orderMult = 1000000” to “Const orderMult = 100000” (one less zero)

 

 

Now you should once again be able to successfully send order to IB via Excel.