Transcription

RIT User GuideBuild 1.01RIT VBA API TutorialTable of ContentsIntroduction . 2Introduction to Excel VBA (Developer) . 3API Commands for RIT . 11RIT API Initialization . 12Algorithmic Trading Example - Arbitrage . 21Copyright 2014, Rotman School of Management. No part of this publication may be reproduced, stored in a retrieval system,used in a spreadsheet, or transmitted in any form or by any means – electronic, mechanical, photocopying, recording orotherwise – without the permission of Rotman School of Management.

IntroductionThe Rotman Interactive Trader allows users to program trading instructions in Microsoft Excel VisualBasic for Applications (VBA) modules. The purpose of this is to allow for program or “algorithmic”trading, where the computer executes trades based on a pre-defined set of instructions orparameters.This tutorial document assumes that the user has no previous knowledge of VBA, and begins bydiscussing the concepts of programming before in-depth trading algorithms are introduced. Thosewho are already familiar with VBA should skip to the section entitled “API commands for RIT”.This document also does not discuss the strategies behind algorithmic trading. Rather, it introducesthe user to the tools that are available through the RIT API. Users are encouraged to explore possiblestrategies and techniques and use the building blocks here to implement them.Copyright 2014, Rotman School of Management.2

Introduction to Excel VBA (Developer)To access the VBA editor in Excel, first ensure that it is turned on by clicking on “File” on the top-leftcorner of the screen, then click on “Options”. Once the “Excel Options” window is opened, choose“Customize Ribbon” on the left menu bar, and ensure that “Developer” on the right side is checked.Once this is checked, the Developer Tab will appear in the original list of Excel tabs.You can access the VBA editor by clicking on the “Visual Basic” icon within the Developer tab.Hint: You can access this at anytime with the shortcut Alt F11Copyright 2014, Rotman School of Management.3

The VBA editor will display all of the loaded Excel projects and add-ins. What is relevant is theVBAProject (Book1) that you are currently working on. Note: Book1 refers to the name of your excelspreadsheet file and will change as you change your filename.We will begin by writing some basic procedures in your Book1.xls. In order to do this, create a modulein your book by going to Insert - Module.Module1 will be added to your Book1 project and a code window will open on the right hand sideallowing you to input your programming code.The first step is to write a very simple procedure. A procedure is a set of programming lines that arerun by the computer whenever instructed to do so. Procedures are defined with the lines “sub procedure ” and “end sub” enclosing them. We will define a procedure named “message” byinputting “Sub message” into the code window. As soon as you type “Sub message” (without quotes)and press enter, VBA will automatically format the text by adding brackets after message and add“End Sub” to the next line.Copyright 2014, Rotman School of Management.4

We have just created a procedure called “message”. When this procedure is run, it will execute thecode. In this case, it will do nothing since we have not written any code between the beginning of theprocedure (sub) and end of the procedure (end sub).We will start with a basic set of code that references the built-in VBA function “MsgBox”. To do this,type “MsgBox (“Hello World”)” into the code window between your (Sub) and (end sub). The”MsgBox” command will cause a pop-up message box to show up in Excel when the code is executed.After you have typed the code into the window, click on the “Play” button in the VBA editor, your codewill execute and a pop-up message in Excel should appear.Copyright 2014, Rotman School of Management.5

You have just completed writing and running a procedure in VBA. Obviously running the procedurefrom the VBA editor is rather cumbersome, so the next step involves linking the macro to an Excelbutton so that it is easier to run the procedure.To create the Macro button, go back to the Developer tab in Excel and click on Insert, and then selectthe first option “Button”.Copyright 2014, Rotman School of Management.6

When you move your mouse over the spreadsheet, the mouse cursor will become a crosshair insteadof an arrow. Click and drag anywhere on the spreadsheet to draw the button. Once you finish drawingthe button, the “Assign Macro” form will appear, select “message” (the name of your macro you justwrote), then click OK. Now that you have assigned the procedure “message” to the button, theprocedure will be executed each time you click the button. Note: If you change the name of yourprocedure, do not forget to re-assign your Macro. In order to re-assign the macro, you will only need toright click on the button and then select “Assign Macro”Once that is complete, left-click on the button and your “Hello World” message box should appear. Ifyou ever want to edit this object (resize, redirect, etc.) right click on it and a context menu will appearallowing you adjust the box.To understand a little bit more behind the programming, we will revisit the code and modify it to beslightly more complex. In the Visual Basic Editor, we are going to modify the code to read “MsgBoxCells(1,1)” instead of “MsgBox (“Hello World”)”.Much like Microsoft Excel, VBA assumes that any text wrapped in “quotes” is plain text, whereasanything not wrapped in “quotes” is a function, procedure, or operation. Since there are no quotesaround “Cells(1,1)”, it will not say “Hello Cells(1,1)”, instead, it will follow the command of Cells(1,1).Copyright 2014, Rotman School of Management.7

The Cells(x,y) command is a function in Excel that instructs VBA to replace itself with the data fromthe spreadsheet row x, column y. Essentially the way VBA interprets this set of code is:MsgBox(“x”) means “Create a message box with the text x”Replacing (“x”) with Cells(1,1) means we will use the data from the cell located in row 1, column 1.MsgBox Cells(1,1) means “Create a message box with the data from row 1, column 1”Now go to the Cell A1 in the current Excel Sheet1 and type in “Bob”. Click on your Macro button, theresult should be a message box that says “Bob”. Hint: If you want to reference cells from other sheets,you can do this by typing Sheet3.Cells(1,1). This will now use the data from cell A1 on Sheet3.We can make this more complex by adding an equation into the procedure. Go back to the VBA editorand change your code to the following:Go to your Excel Sheet and type “Sally” into Cell A2, and click your macro button. The result shouldbe:Copyright 2014, Rotman School of Management.8

To clean this up a little bit, we will make another adjustment to the code by adding the word “and”between the two references. This is accomplished as follows:Notice the quotes around the word “and”, as well as the space between the quotes and the word “ and”. Without the spaces, the message box would simply say “BobandSally”. Alternatively without the“quotes” around and , VBA would think “and” is a command instead of using it as “text”.The last code adjustment that we will make is to add a mathematical equation to our message box.This is accomplished as follows:Type the values “3” and “5” into cells A3 and A4 and run your procedure by clicking the button. Theresult should be “Bob and Sally15”. Since we used the asterisk “*” between Cells(3,1) and Cells(4,1),VBA is instructed to multiply the values from these two cells, and then append them as text to therest of the text.Copyright 2014, Rotman School of Management.9

This concludes the basic VBA training that you will need in order to access the RIT API. You are nowable to write a simple set of instructions (a procedure) in VBA using a predesigned function (MsgBox)and execute it via the Button that was created. In the next section, you will use the skills that you havelearned, and apply them to trading!Copyright 2014, Rotman School of Management.10

API Commands for RITSetting up RIT API configurationApplication Programming Interface (API) commands in Excel VBA can both retrieve informationfrom and perform actions on the Rotman Interactive Trader (RIT).To begin, start with a NEW spreadsheet and access VBA. In order to access RIT‟s built-in VBAcommands, you will need to add it as a reference to your VBA project by going to: Tools - ReferencesWhen the Reference window appears, scroll down and check the item “Rotman Interactive Trader”.This step loads the Rotman commands and functions into VBA so that you can reference them.Next, create a module in your file by going to Insert - Module.Copyright 2014, Rotman School of Management.11

RIT API InitializationThen, initialize a new Rotman Interactive Trader API object using the following code:Dim API As RIT2.APISet API New RIT2.APIOnce the RIT API object is initialized, you can start writing API commands. In general, the syntax foran API command is made up of 3 main parts: the object, the method, and the parameter(s) (optional),as demonstrated in the following sample code:In this example, API is the object that actions are performed on. The method, CancelOrder, is theaction to perform on API (in this case, the action is to cancel an order). The parameter, order id,specifies details of the action (here, it specifies the order ID of the particular order to cancel).Depending on the action that a method performs, it may or may not require a parameter. In theexample above, API.CancelOrder requires a parameter to specify which order to cancel. In thefollowing sections you will see examples of methods which do not require a parameter. Thesemethods perform general actions. There are also examples demonstrating the use of more than oneparameter, separated by a comma.Other than performing actions, methods can also return a result (called the return value). It can bestored in a variable or a cell in an Excel worksheet for later reference. The example API.CancelOrderdoes not have a return value.Submitting an OrderThe following command adds an order to RIT.General command Syntax:API.AddOrder(ticker, size of trade, price of trade, buy/sell, lmt/mkt)Copyright 2014, Rotman School of Management.12

Parameters:Parametertickersize of tradeprice of tradeDescriptionTicker symbol of a stockBid size or ask sizeBid price or ask price*buy/sellBuy or sell an orderlmt/mktType of an orderPossible Values“ALGO”, “CRZY”, Range(“A1”), etc.500, 10, Range(“A1”), Cells(2,3), etc.10.00, 15.25, Range(“A1”), Cells(3, 4), etc.Buy order: API.BUY or 1**Sell order: API.SELL or -1**Limit orders: API.LMT or 1**Market orders: API.MKT or 0*** When inputting a market order, the price of trade must be specified with an arbitrary number. Thisnumber will be ignored as all market orders transact at the market price. See example in sample code2.**While you can code the buy and sell parameters directly with API.BUY and API.SELL, or indirectlywith 1 and -1, if you are referencing cells you must use 1 (for buy) and -1 (for sell). You will get anerror if you reference cells containing the corresponding text values API.BUY and API.SELL.The same applies to referencing lmt mkt parameters. See example in sample code 3.Let’s start by simply submitting a buy order. This can be accomplished with the following code:Sub submitorder()Dim API As RIT2.APISet API New RIT2.APIDim status as Variantstatus API.AddOrder("CRZY", 1000, 5, API.BUY, API.LMT)End SubNote that the example is setup assuming that students are trading a case with a stock “CRZY”. If youare trading a different case, you will need to change the ticker otherwise the command will not worksince the security “CRZY” does not exist.As you type the beginning of the command “API”, you will notice that a dropdown box will appearshowing all of the different API commands that you can access.Copyright 2014, Rotman School of Management.13

You will also notice that as you type in the API.AddOrder command, a tooltip will show you thedifferent command line parameters that are required for the API.AddOrder command.Once you have completed the code, you can click on the red Play button in order to run the procedure.Click the button a few times and visit your RIT Client, you should see limit orders placed at 5.00 tobuy shares of CRZY.Return Value: True or FalseThere are a few sample codes you can try in order to practice submitting different types of orders.Please feel free to try them.Sample Code 1 – Limit Order:Submit a limit buy order for the stock CRZY with size 1000, at a price of 5.00. Assign True to thevariable status if the order is successful, and assign False otherwise. Use “Range” to call cells thatcontain volume and price information. (So in this case, you should type 1000 in cell A1, and type 5 inCopyright 2014, Rotman School of Management.14

cell A2 as they are referenced for volume and price, respectively.) Note that Alternative 2 uses 1instead of API.BUY and 1 instead of API.LMT.Alternative 1:Dim status as variantstatus API.AddOrder("CRZY", Range("A1"), Range("A2"),API.BUY, API.LMT)Alternative 2:Dim status as variantstatus API.AddOrder("CRZY", Range("A1"), Range("A2"), 1, 1)Sample Code 2 – Market Order:Submit a market sell order for the stock CRZY with the size found in the cell A1 at the market price.Assign True to the variable status if the order is successful, assign False otherwise. Note that the sellprice is specified here (with an arbitrary number, 1) even though it is ignored.Alternative 1:Dim status as variantstatus API.AddOrder("CRZY", Range("A1"), 1, API.SELL,API.MKT)Alternative 2:Dim status as variantstatus API.AddOrder("CRZY", Range("A1"), 1, -1, 0)Sample Code 3 – Referencing Cells for buy sell:Submit an order for the stock CRZY with the size found in the cell A1 at the market price. Assign Trueto the variable status if the order is successful, assign False otherwise. Whether the market order isto sell or buy depends on the value in the cell A2. Note that if a cell reference is used for the buy sellparameter, the number value must be used in the cells. In other words, the cell A2 must contain 1 or-1. The strings “API.BUY” or “API.SELL” will not work.Referencing cells for the lmt mkt parameter follows the same pattern. The cell being referenced mustcontain 0 or 1 instead of the text “API.LMT” or “API.MKT”.Dim status as variantstatus API.AddOrder("CRZY", Range("A1"), 1, Range(“A2”), 0)Copyright 2014, Rotman School of Management.15

Sample Code 4 – Using AddQueuedOrder:Similar to AddOrder, you can also use AddQueuedOrder to submit a limit or market, buy or sell order.While all the parameters for AddQueuedOrder are the same as for AddOrder, the difference lies inthe return value. While AddOrder returns True/False, AddQueuedOrder will return -1 (for failure tosubmit an order when the case is inactive) or an internal queued order ID* (for successful ordersubmission).Dim status as variantstatus API.AddQueuedOrder("CRZY", 1000, 5, API.BUY, API.LMT)*When an order is submitted using either AddOrder or AddQueuedOrder API command, the RITServer ‘queues’ an order before processing it in the system. Hence, when each order is queued, aninternal queued order ID is first provided, and is converted later to an order ID when it appears onthe Market Depth Book. This entire order submission process is generally completed in a fraction ofa second when there are not many orders. However, one may choose to specifically useAddQueuedOrder in order to retrieve an internal queued order ID and cancel it individually beforean order is processed. For more detailed information, please refer to ‘Sample Code 3 – UsingCancelQueuedOrder’ under the ‘Cancelling an Order’ section below.In addition, you can use the IsOrderQueued command to see if any particular order is currentlyqueued. The command requires an internal queue ID as an input, and returns “True” for the orderthat is queued (at the moment), and “False” for any orders that are not queued (i.e. whether the orderhas been queued previously but successfully submitted, or simply the order has failed to be queued).Dim status as variantstatus API.AddQueuedOrder("CRZY", 1000, 5, API.BUY, API.LMT)API.IsOrderQueued(status)From the above example, the IsOrderQueued command will return “False” because by the time thatthe VBA code reaches the “API.IsOrderQueued(status)” line, the order has been alreadyqueued and submitted from the API.AddQueuedOrder command. Hence, the command will return“False” since the order is not queued anymore. If there are several orders submitted by the API code,the IsOrderQueued command may return “True” if it is still queued.Cancelling an OrderThe following command cancels an order based on the order ID specified by the parameter.General command Syntax:API.CancelOrder (order id)Copyright 2014, Rotman School of Management.16

Parameters:ParameterOrder idDescriptionOrder ID*Possible Values3142, 2323, Range(“A1”), etc.*Order IDs can be retrieved via the RTD functions – refer to the “Grabbing Ticker Specific Data Fields”section from the RIT - User Guide - RTD Documentation.pdf.Return Value: NoneThere are a few code samples you can try in order to practice cancelling orders. Please make surethat you have submitted orders before you try cancelling them.Sample Code 1:Cancel the order with the Order ID 1500. Usually, you would make this more robust by linking thevalue to a cell location with the Cells(x,y) or Range(“mx”) functions as in Sample Code 2.Sub cancelorder()Dim API As RIT2.APISet API New RIT2.APIAPI.CancelOrder (1500)End SubSample Code 2:Cancel the order specified in cell A1API.CancelOrder (Range(“A1”))Sample Code 3 – Using CancelQueuedOrder:You can use CancelQueuedOrder to cancel an order that is ‘queued’ on the RIT Server before itappears on the Market Depth Book. Once you retrieve an internal order ID using AddQueuedOrderAPI command (from the ‘Sample Code 4 – Using AddQueuedOrder’ under ‘Submitting an Order’section), you can use the following command to cancel it:API.CancelQueuedOrder (internal queued order ID)In case you would like to cancel all queued orders, you can use the following command:API.ClearQueuedOrdersAgain, please note that the above API commands only cancel the queued orders before they appearon the Market Depth Book. In order to cancel the orders that are submitted and visible on the MarketCopyright 2014, Rotman School of Management.17

Depth Book, please use the API.CancelOrder commands from above or follow the Cancel OrderExpression instructions below.Cancel Order ExpressionThe following command cancels all orders that satisfy the expression specified in the parameter.General command Syntax:API.CancelOrderExpr (order expr)Parameters:Parameterorder exprDescriptionOrder expressionPossible Values*“Price 20.00”, “Volume 400”,“ticker ‘CRZY’”,“Price 20.00 AND Volume 400”,“Price 20.00 AND Volume 400”, etc.* Available operators include (equal to), (not equal to), (greater than), (less than), (greater orequal to), (less than or equal to). You may also use brackets “()” to clarify order of operations.Return Value: NoneSample Code 1:Cancel all orders that have a price greater than 20.00 and a volume equal to 400.API.CancelOrderExpr (“Price