This lesson will go over the advanced functionality of the TWS sample spreadsheet, including placing orders.
In Lesson 3, the DDE sample spreadsheet has a variety of functionalities which demonstrates most of the capabilities of the API.
In this lesson, we will discuss some of the more advanced functionality of the spreadsheet, including placing orders and bracket orders. We will also discuss what goes on “under the hood” of the sample spreadsheet.
Please note – the sample spreadsheet is not meant to be used as a robust trading application. We will discuss the logic of order placement to show what is possible with DDE and VBA coding.
- First, navigate to the Basic Orders tab of the DDE spreadsheet.
- Enter all the necessary fields for the contract of interest, whether it is a stock, future, option, etc. Lesson 3 provides a more in-depth look at contract descriptions.
- Once that is set up, enter the parameters for the “Order Description.”
- As shown in row 12, this is an order to Buy 700 shares of Ford as a limit order for this price.
- After you are satisfied with your order, highlight the symbol(s) of interest, then click “Place/Modify Order” on the top left.
- This is the equivalent of entering a limit order for symbol F via the TWS order entry window.
- Once submitted successfully, the limit order will be sent to TWS and will be working on the exchange as a manually submitted order.
You can define a bracket order which is designed to help capture profit and help minimize loss. A BUY order is bracketed by a high-side sell limit order and a low-side sell stop order. A SELL order is bracketed by a high-side buy stop order and a low side buy limit order.
To get started creating our bracket in Excel, we can look at the API documentation for brackets.
- In this basic example of a bracket, we will be using one parent order and two child orders.
- Note – all the orders have Transmit=False, except the last child order to transmit the whole bracket, and the child orders have a parentId field to tie the orders together.
- This same logic can be expanded for custom bracket orders, for example, if you wanted more than two child orders.
- In this example, we can use the pre-existing order in row 14 for a limit order to buy GOOG.
- This will serve as our parent order.
- Next, set the Transmit flag for the parent order via the “Extended Order Attributes” tab.
- This tab allows the underlying sample code to set multiple parameters or fields for selected orders.
- In this case we are interested in the parameters in rows 73-74.
- For this parent, enter 0 for row 73, where the logic is 0 equals false and 1 is true.
- On the “Basic Orders” tab, select GOOG on row 14, then click on the macro “Apply Extended Template”.
- The macro will fill in the fields you previously specified in the template.
- Click on “Place/Modify Order” to submit the parent.
- Once the order is sent successfully, you would see the order in the TWS order monitor with a blue “Transmit” button next to it.
First Child Order
- We can now move on to the first child order.
- We can use the next row for this child order.
- First, we copy our contract from row 14, as the orders in the bracket will use the same contract.
- This first child we define is the profit taker example. In the order description, we’ll enter a SELL LMT order for this price.
- Using the “Extended Order Attributes” tab, we’ll copy the generated parent order id onto line 74 –the “parent order id”. Make sure to copy the number only.
- Keep Transmit set to 0.
- Click on Apply Extended Template.
- Now that the child is set up, you may submit the order.
- The child order appears in the TWS order monitor with a blue transmit.
Last Child Order
- The last child, the stop loss order, can now be set up. For this, we can set it up in the next line. First enter the contract description.
- Then, to make it a stop order, enter STP for order Type, leave Limit Price blank, and enter your stop price in the auxiliary price field.
- On the Extended Order Attributes tab – we’ll keep the parent id and set Transmit to 1 to send the whole bracket once this child order is placed.
- Click on Apply Extended Template.
- Place the last child order.
- If all the orders were sent correctly, without errors, now you should see all 3 orders transmitted in the TWS order monitor.
- You can confirm they are part of a bracket via the “key” column in TWS.
Here’s a tip – instead of using the Extended Order Attributes tab for the bracket order setup – you can scroll to the right on the Basic orders tab and place the values into these columns.
The sample’s underlying VBA code
Compared to the legacy DDE sample spreadsheet, the new DDE sample relies heavily on programmed functions in VBA code to perform actions.
For example, when requesting historical data in the DDE sample, VBA code must be used to process incoming bars and place them in the appropriate cells. The creation of studies and indicators and custom conditional orders are other common uses of VBA. To view the code, press Alt+F11 from any page in the spreadsheet. The Visual Basic Editor opens.
The code window will then display the code for the immediate worksheet, in this historical data. For those of you who are unfamiliar with the Visual Basic Editor, there are three main areas of the Visual Basic Editor window:
- Project Explorer
- Properties Window
- Code Window
You can see the Microsoft Excel Objects in the Project Window that correspond to the pages in the spreadsheet. Double-click any of them to display the code for that page. There are also additional forms and code modules used by the rest of the code that you can see if you scroll down in the Project Window.
Reviewing the underlying VBA code is advised when trying to create your own custom sheet and automating functions. Further programming instruction and support is outside the scope of this tutorial and IBKR API Support.
The other Microsoft Excel feature used throughout the TwsDde.xls spreadsheet is the macro.
- Every button on every page in the spreadsheet has a macro associated with it.
- You can see the macros used in the spreadsheet by viewing the list of macros, then clicking Edit to open the macro in the Visual Basic Editor to look at the actual code.
- You may view Macros by navigating to the “View” tab of the ribbon, click Macros, then “View Macros”.
- Once you find a macro button of interest, you may select it and click on Edit to review the code. Here is the code for the requestHistoricalData button for example.
Disclosure: Interactive Brokers
The analysis in this material is provided for information only and is not and should not be construed as an offer to sell or the solicitation of an offer to buy any security. To the extent that this material discusses general market activity, industry or sector trends or other broad-based economic or political conditions, it should not be construed as research or investment advice. To the extent that it includes references to specific securities, commodities, currencies, or other instruments, those references do not constitute a recommendation by IBKR to buy, sell or hold such investments. This material does not and is not intended to take into account the particular financial conditions, investment objectives or requirements of individual customers. Before acting on this material, you should consider whether it is suitable for your particular circumstances and, as necessary, seek professional advice.
Supporting documentation for any claims and statistical information will be provided upon request.
Any stock, options or futures symbols displayed are for illustrative purposes only and are not intended to portray recommendations.
Disclosure: API Examples Discussed
Throughout the lesson, please keep in mind that the examples discussed are purely for technical demonstration purposes, and do not constitute trading advice. Also, it is important to remember that placing trades in a paper account is recommended before any live trading.