Need some help with automated IB TWS order entry from a simple Excel spreadsheet. - IB Trader Workstation | futures trading

Go Back

> Futures Trading, News, Charts and Platforms > Platforms and Indicators > IB Trader Workstation

Need some help with automated IB TWS order entry from a simple Excel spreadsheet.
Started:October 16th, 2014 (12:17 AM) by Snorkel Views / Replies:1,148 / 2
Last Reply:October 25th, 2014 (06:38 PM) Attachments:0

Welcome to

Welcome, Guest!

This forum was established to help traders (especially futures traders) by openly sharing indicators, strategies, methods, trading journals and discussing the psychology of trading.

We are fundamentally different than most other trading forums:
  • We work extremely hard to keep things positive on our forums.
  • We do not tolerate rude behavior, trolling, or vendor advertising in posts.
  • We firmly believe in openness and encourage sharing. The holy grail is within you, it is not something tangible you can download.
  • We expect our members to participate and become a part of the community. Help yourself by helping others.

You'll need to register in order to view the content of the threads and start contributing to our community. It's free and simple, and we will never resell your private information.

-- Big Mike

Thread Tools Search this Thread

Need some help with automated IB TWS order entry from a simple Excel spreadsheet.

Old October 16th, 2014, 12:17 AM   #1 (permalink)
Trading Apprentice
Hartford CT
Futures Experience: Advanced
Platform: IB TWS
Favorite Futures: Stocks
Posts: 3 since Oct 2014
Thanks: 1 given, 0 received

Need some help with automated IB TWS order entry from a simple Excel spreadsheet.

Need some help with IB TWS automated order entry from a simple Excel spreadsheet.

Situation can be reduced to the following:
Assume the following are in adjacent columns (items in parens are constant):
Ticker (e.g., AAPL, MSFT, etc.)
Security Type (STK)
Exchange (SMART)
Currency (USD)
Action (BUY or SELL)
Order Type (LMT)
Limit Price

Assume any number of adjacent rows, each for a separate stock.

The Quantity values for each stock are computed based on factors elsewhere in the spreadsheet, but are always zero EXCEPT when a trigger event has occurred, in which case either a buy or a sell order must be generated and submitted for the now non-zero number of shares. This must be done automatically under program control, and not by pressing a control button.

What I need is for a routine which will loop through the rows once every nn seconds and, should there have been
computed a non-zero quantity in the share value cell for any stock, submit the appropriate buy or cell order,
execution of which will--due to automated computations elsewhere in the spreadsheet--revert to zero upon the Order being filled.

Timing is not an issue, as I have a subroutine which updates data elsewhere in the spreadsheet every 30 seconds; I can easily call the order entry subroutine during this other process.

What I am having trouble with (I'm new to VBA and DDE but have some now antiquated programming knowledge from long before many who will read this were born--OK, 1975 and later years.....) is figuring out the precise syntax I need to use.

The closest thing I could find is the module code below, which I copied from an example in an ancient IB DDE guide
for newbies entitled "ExcelApiBeginners.pdf"). Unfortunately, though the pdf is still available online (well
hidden), the sample spreadsheet it references no longer exists at the link referenced by the pdf.

Note that the sample code below is intended to generate trades based on a computed P&L trigger. Nonetheless I am certain that it can be adapted to meet my needs. Unfortunately, the key line in the code is lengthy, and the right-hand side of it is cut off in the screenshot embedded in the pdf. Sill, the rest of the code is clearly pertinent.

I'd really appreciate any help you all could render in adapting it meet to my simple need as described above.

Here's the code; thanks in advance for any help or guidance you can give me, the more detailed an explicit the better.


Option Explicit
Dim lastId As Long
Dim offset As Long
Public runWhen As Double
Public Const RUN_INTERVAL_SECONDS = 900 ' 5 minutes
Public Const RUN_WHAT = "Example1.automateTrade"
Public Const P_AND_L_TRIGGER_VALUE = 50

Sub automateTrade()
Dim symbol As String
Dim secType As String
Dim expiry As String
Dim strike As String
Dim right As String
Dim currencyCde As String
Dim position As Integer
Dim unrealizedPandL As Integer
Dim realizedPandL As Integer
Dim logSuccess As Integer
Dim logSuccess As Boolean
Dim portfolioRow As Integer
Dim lastPortfolioRow As Integer

' Following statement does not compile; no idea why as it seems to match the code in the pdf

lastPortfolioRow = ExampleUtil.getLastDataRow("Portfolio", 8 )

' For each record in Portfolio, make market order to SELL if:
' 1) there are open positions
' 2) unrealized P&L + realized P&L > X
For portfolioRow = 8 To lastPortfolioRow
symbol = UCase(Worksheets("Portfolio").Cells(portfolioRow, 1).Value)
secType = UCase(Worksheets("Portfolio").Cells(portfolioRow, 2).Value)
expiry = Worksheets("Portfolio").Cells(portfolioRow, 3).Value
strike = Worksheets("Portfolio").Cells(portfolioRow, 4).Value
right = UCaseWorksheets("Portfolio").Cells(portfolioRow, 5).Value
currencyCde = UCase(Worksheets("Portfolio").Cells(portfolioRow, 6).Value)
position = CInt(Worksheets("Portfolio").Cells(portfolioRow, 8).Value)
unrealizedPandL = CInt(Worksheets("Portfolio").Cells(portfolioRow, 12).Value)
realizedPandL = CInt(Worksheets("Portfolio").Cells(portfolioRow, 13).Value)

If position > 0 Then
If (unrealizedPandL + realizedPandL) > P_AND_L_TRIGGER_VALUE Then

' Following IF statement is incomplete and will not compile, perhaps because I do not have the code for createOrder?
' Need extra help here!

If ExampleUtil.createOrder("SELL", symbol, secType, expiry, strike, right, currencyCode, position)
logSuccess = logMessage("[automateTrade]", "Automated market SELL order successfully created"
End If
End If
End If
Next portfolioRow

startTimer ' schedule next run
End Sub

Below is the text which accompanies the code above in IB's ExcelApiBeginners.pdf

Download the Samples

Download the TwsDdeBeginners.xls spreadsheet right onto your computer to have
instant access to the code and the new worksheets.

To get the spreadsheet, go to:[
Then click Beginner’s Guide to display the page with the downloadable samples.

Example 1 : Sell based on P & L

The code for this macro is in the Example1 module. Use the Alt + F11 hotkey
combination to open the VBE editor, and in the Project Explorer open the
Modules directory and double click Example1.

Modify the P&L Trigger Value
The purpose of this sample is to sell open positions in your portfolio that have:
Realized P&L + Unrealized P&L >= a pre-defined value. This pre-defined value
is stored in a variable called P_AND_L_TRIGGER_VALUE and is set to 50. You
can modify the value of the P&L Trigger Value variable by changing the value in
the declaration:
Public Const P AND L TRIGGER VALUE = 50.

Limit the Sample to a Specific Stock
By default, the sample goes through your entire portfolio, but you can customize
it to look at just stocks, a specific stock, or any other criteria you choose. For
example, to limit the sample to just Microsoft stock1, add the following code
highlighted below in bold:

If position > 0 And secType = “STK” And symbol = “MSFT”
If (unrealizedPandL + realizedPandL) >
If ExampleUtil.createOrder("SELL", symbol, secType,
expiry, strike, right, currencyCde, position, "", "P&L")
logSuccess = logMessage("[automateTrade]", "Automated
market SELL order successfully created for: " & symbol)
End If
End If
End If

Save your changes. When the trigger value is reached, a sell market order is
created. This information is logged on the worksheet titled “Auto Orders.” The
results of the order will then appear on the other worksheets that have subscribed
to account changes, for example, “Executions” and “Portfolio”.

Enable or Start the Subroutine
This subroutine runs in the background. It can be enabled when first opening the
spreadsheet or enabled by clicking the Start button on the “Auto Orders” screen
for “P&L” (scroll to the right until you see the button). The scheduling is
controlled by the variables RUN_WHAT and RUN_INTERVAL_SECONDS.
RUN_WHAT contains the macro name, i.e., Example1.automateTrade and
RUN_INTERVAL_SECONDS contains how often the macro should run, i.e., 900
seconds (15 minutes).

Code Summary

automateTrade() – The subroutine that monitors
P&L for your open positions
startTimer() – Starts the P&L background macro
stopTimer() – Stops the P&L background macro

Contains common functions for the examples

New Worksheet(s)
The “Auto Orders ” page was created to log the orders that are initiated by
background macros. It also contains controls to start/stop the background macros.

Reply With Quote

Old October 22nd, 2014, 08:51 PM   #2 (permalink)
Trading Apprentice
Hartford CT
Futures Experience: Advanced
Platform: IB TWS
Favorite Futures: Stocks
Posts: 3 since Oct 2014
Thanks: 1 given, 0 received

Figured it out. I have no interest in the TWSDDE.xlsm files. All I really wanted was a method to extract current price and position info into a spreadsheet of my own design in real time, so that trades could be triggered automatically based on computations taking place continuously elsewhere in my Excel spreadsheet. What I finally figured out is that by constructing a text string containing the appropriate DDE instructions (sample below) from information contained elsewhere in my spreadsheet and placing that string in a spreadsheet cell (without the prepended "=" sign, that I could then use the EVALUATE function in a very simple VBA subroutine (google the words "EVALUATE" AND "MyEval" for code references) to prepend the "=" sign (placing the result in an adjacent cell) I could convert the string into an executable "command".


No muss; no fuss; no bother. Oh...."one more thing!" (R.I.P. Steve Jobs).....having set up such a data retrieval and trade submission method for a single stock, I can then simply duplicate (i.e., copy) the cells containing the code into other spreadsheet areas, allowing my to process info for a potentially endless list of stocks without doing anything more than listing the tickers in a spreadsheet column and then replicating the "coding" cells down the spreadsheet in adjacent columns by simple copy.

I would not have dwelt so much on this approach were it not for the fact that nowhere on the 'net have I seen anyone make reference to using IB TWS DDE other than within the confines of the IB-supplied TWSDDE.xlsm spreadsheet or extracts therefrom.

For critics, I have of course dealt in a similar and simple fashion with the need for unique ascending trade "id's", etc. The brief description is not intended to be "all things" to anyone, but instead more of an conceptual advisory; those who may follow (or precede) me down this path must of course exercise due care to ensure that, once at the end, the destination at which they've arrived is that which they intended at the outset. I only wish that another intrepid traveler had left me a trail of breadcrumbs to follow; my journey would've been ever so much easier had I not been forced to spend so much effort (however fruitful in the end) beating about the undergrowth.

Last edited by Big Mike; December 9th, 2014 at 12:20 PM. Reason: request
Reply With Quote

Old October 25th, 2014, 06:38 PM   #3 (permalink)
Trading Apprentice
Hartford CT
Futures Experience: Advanced
Platform: IB TWS
Favorite Futures: Stocks
Posts: 3 since Oct 2014
Thanks: 1 given, 0 received

An additional helpful hint:
If you modify the order2 subroutine within the OrderFunctions module of TwsDde.xls and place the following single line of code just above or below above the " 'create order" remark, the exact syntax of the order which would be submitted (including any extended attributes you've specified) when you press the "Place/Modify" button on the BasicOrders sheet will be displayed in cell A6 of that sheet, just below your TWS id. You can then write your own code to construct and submit appropriately formatted order specification strings incorporating whatever extended attributes you wish.

orderSheet.Cells(6, 1).value = req

Reply With Quote

Reply > Futures Trading, News, Charts and Platforms > Platforms and Indicators > IB Trader Workstation > Need some help with automated IB TWS order entry from a simple Excel spreadsheet.

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Upcoming Webinars and Events (4:30PM ET unless noted)

NinjaTrader 8: Programming Profitable Trading Edges w/Scott Hodson

Elite only

Anthony Drager: Executing on Intermarket Correlations & Order Flow, Part 2

Elite only

Adam Grimes: Five critically important keys to professional trading

Elite only

Machine Learning Concepts w/FIO member NJAMC

Elite only

MarketDelta Cloud Platform: Announcing new mobile features

Dec 1

NinjaTrader 8: Features and Enhancements

Dec 6

Similar Threads
Thread Thread Starter Forum Replies Last Post
TWS and Excel hadamkov IB Trader Workstation 11 January 29th, 2016 06:08 AM
IB order entry grew MultiCharts 7 February 28th, 2014 01:16 PM
Using TWSStart to keep IB TWS open arjfca Brokers and Data Feeds 11 April 5th, 2012 08:37 AM
Trading Journal example (Excel spreadsheet) Big Mike Psychology and Money Management 20 March 16th, 2012 08:26 PM
AUTOMATED ORDER ENTRY using my script(so my computer enters automatically)POSSIBLE??? Rad4633 NinjaTrader Programming 3 September 20th, 2011 02:03 AM

All times are GMT -4. The time now is 03:43 AM.

Copyright © 2016 by All information is for educational use only and is not investment advice.
There is a substantial risk of loss in trading commodity futures, stocks, options and foreign exchange products. Past performance is not indicative of future results.
no new posts

Page generated 2016-10-26 in 0.12 seconds with 19 queries on phoenix via your IP