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

Need some help with automated IB TWS order entry from a simple Excel spreadsheet.
Updated: Views / Replies:1,886 / 2
Created: by Snorkel Attachments:0

Welcome to futures io.

(If you already have an account, login at the top of the page)

futures io is the largest futures trading community on the planet, with over 90,000 members. At futures io, our goal has always been and always will be to create a friendly, positive, forward-thinking community where members can openly share and discuss everything the world of trading has to offer. The community is one of the friendliest you will find on any subject, with members going out of their way to help others. Some of the primary differences between futures io and other trading sites revolve around the standards of our community. Those standards include a code of conduct for our members, as well as extremely high standards that govern which partners we do business with, and which products or services we recommend to our members.

At futures io, our focus is on quality education. No hype, gimmicks, or secret sauce. The truth is: trading is hard. To succeed, you need to surround yourself with the right support system, educational content, and trading mentors – all of which you can find on futures io, utilizing our social trading environment.

With futures io, you can find honest trading reviews on brokers, trading rooms, indicator packages, trading strategies, and much more. Our trading review process is highly moderated to ensure that only genuine users are allowed, so you don’t need to worry about fake reviews.

We are fundamentally different than most other trading sites:
  • We are here to help. Just let us know what you need.
  • We work extremely hard to keep things positive in our community.
  • We do not tolerate rude behavior, trolling, or vendors advertising in posts.
  • We firmly believe in and encourage sharing. The holy grail is within you, we can help you find it.
  • 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.

-- Big Mike, Site Administrator

Thread Tools Search this Thread

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

  #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
  #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
  #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


futures io > > > > 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)

Wyckoff Hunting for Great Risk/Reward Ratio w/Gary Fullett

Elite only

Digging into the Details of iSystems w/Stage 5 & iSystems

Jun 5

Similar Threads
Thread Thread Starter Forum Replies Last Post
Trading Journal example (Excel spreadsheet) Big Mike Psychology and Money Management 22 April 21st, 2018 02:00 AM
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 Reviews of Brokers and Data Feeds 11 April 5th, 2012 08:37 AM
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:53 PM.

Copyright © 2018 by futures io, s.a., Av Ricardo J. Alfaro, Century Tower, Panama, +507 833-9432,
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 2018-05-20 in 0.13 seconds with 19 queries on phoenix via your IP