NexusFi: Find Your Edge


Home Menu

 





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


Discussion in Platforms and Indicators

Updated
    1. trending_up 3,431 views
    2. thumb_up 0 thanks given
    3. group 1 followers
    1. forum 2 posts
    2. attach_file 0 attachments




 
Search this Thread

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

  #1 (permalink)
Snorkel
Hartford CT
 
Posts: 3 since Oct 2014
Thanks Given: 1
Thanks Received: 0

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)
Quantity
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:
individuals.interactivebrokers.com/en/p.php?f=programInterface&a&ib_ent[
ity=lic.(NOTE: THIS IS A BAD LINK TO A NO LONGER EXISTING PAGE)
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”
Then
If (unrealizedPandL + realizedPandL) >
P_AND_L_TRIGGER_VALUE Then
If ExampleUtil.createOrder("SELL", symbol, secType,
expiry, strike, right, currencyCde, position, "", "P&L")
Then
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
Module(s)/Subroutine(s):

Example1
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

ExampleUtil
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

Can you help answer these questions
from other members on NexusFi?
My NT8 Volume Profile Split by Asian/Euro/Open
NinjaTrader
New Micros: Ultra 10-Year & Ultra T-Bond -- Live Now
Treasury Notes and Bonds
ZombieSqueeze
Platforms and Indicators
Deepmoney LLM
Elite Quantitative GenAI/LLM
Better Renko Gaps
The Elite Circle
 
  #2 (permalink)
Snorkel
Hartford CT
 
Posts: 3 since Oct 2014
Thanks Given: 1
Thanks Received: 0

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

pprtrdxxx|ord!'id932375459?place?MSFT_STK_SMART_USD_~/BUY_100_REL_0_0.02_~_DAY_~_~_O_0_~_1_~_0_0_0_0_~_0_0_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_0'

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.

Reply With Quote
  #3 (permalink)
Snorkel
Hartford CT
 
Posts: 3 since Oct 2014
Thanks Given: 1
Thanks Received: 0


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




Last Updated on October 25, 2014


© 2024 NexusFi™, s.a., All Rights Reserved.
Av Ricardo J. Alfaro, Century Tower, Panama City, Panama, Ph: +507 833-9432 (Panama and Intl), +1 888-312-3001 (USA and Canada)
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.
About Us - Contact Us - Site Rules, Acceptable Use, and Terms and Conditions - Privacy Policy - Downloads - Top
no new posts