• Overview
• Installation
• Login
• RTD
• IQ
• Examples

Overview

MotifXL is a Microsoft Excel add-in which allows Motionite data to be easily incorporated into Excel spreadsheets. It allows realtime security data to be dynamically displayed using RTD formulas. It allows [IQ] statements to be easily executed in VBA allowing IQ to be used to display data and place orders.

With MotifXL:

• Traders can create spreadsheets to implement their trading strategies and automatically place orders,
• Advisers can create spreadsheets with the data required to manage their portfolios,
• Brokers can bulk import trading instructions and automatically place the associated order requests,
• Software developers can quickly learn IQ and prototype their applications.
• Excel developers can customize Excel to develop powerful trading tools and/or brokerage workflow tools,

Installation

Install MotifXL Add-in

To install MotifXL, follow these steps:

From installer (preferred)

• Download the https://download.motifmarkets.com/motifxl/MotifXL.msi from our site and run it.

Manually

1. Work out whether you are running 32 bit or 64 bit Excel. This can be determined in recent versions of Excel by:

   1. Starting Excel
   2. Clicking File
   3. Clicking Account
   4. Clicking “About Excel”

2. Download the corresponding MotifXL Add-in:

   • https://download.motifmarkets.com/motifxl/MotifXL-32bit-pkg.xll for 32 bit Excel
   • https://download.motifmarkets.com/motifxl/MotifXL-64bit-pkg.xll for 64 bit Excel

3. Copy the downloaded .xll file to a folder where you intend to store it. Any folder on a local drive can be used however we recommend one of the following locations:
   
   For 64 bit:
   C:\Program Files\MotifMarkets\MotifXL or
   C:\Program Files\Microsoft Office\Office16\Library
   For 32 bit:
   C:\Program Files (x86)\MotifMarkets\MotifXL
   or
   C:\Program Files (x86)\Microsoft Office\Office16\Library
   
   Replace Office16 with the version of Microsoft Office installed on your computer.

4. Start Excel and navigate to:
   File | Options | Add-ins

5. At the bottom of the “Add-ins” window, in the “Manage” dropdown, select “Excel Add-ins”. Then press “Go”.

6. In the “Add-ins” dialog, click “Browse” and then select the downloaded .xll from the folder in which it was stored (see above steps).

7. Press “Ok” to exit the “Add-ins” window and return to the spreadsheet.

The MotifXL Add-in should now be installed in Excel. The Excel main menu you should now have another menu item called “MotifXL”. Clicking on this menu item will show the ribbon bar controls for MotifXL.

Show Developer Menu Item

To take full advantage of the MotifXL add-in, you will need to use Excel’s “Visual Basic” and “Macros” capabilities. These can be accessed with the “Developer” main menu item. However, by default, this menu item is not shown. To show the “Developer” menu item, follow the steps below:

1. Start Excel and navigate to:
   File | Options | Customize Ribbon

2. On the right hand side under “Main Ribbon”, make sure that “Developer” is ticked.

3. Click “Ok” to exit “Options” and returen to spreadsheet.

The main menu should now include the “Developer” menu item.

Login

The MotifXL addin provides 2 ribbon controls to facilitate login:

1. Login button
2. Information hover point

To login, simply click the login button. A dialog will appear requesting which Motionite service you wish to login in to and your username and password. Enter these and press “Ok”. If the credentials are correct, the dialog will disappear and you will be logged in.

After you have logged in, you can hover the mouse over the “Information” control and a popup will show which server you are connected to.

RTD

The standard Excel RTD function can be used to display realtime information for any of the streaming topic available. The RTD functions will use the following fields:

    =RTD("MotifXL.RTDServer",,"<topic>","<key>","<field>")
    or
    =RTD("MotifXL.RTDServer",,"<topic>","<key>","<field>","<index>")

where

• <topic> is the type of streaming data.
• <key> is the key value for the specific item of data
• <field> is the field whose value is to be displayed in the cell.
• <index> is an selector used for tabular results.

The value displayed in a cell will be updated in real-time as it changes.

Note that MotifXL will ignore the case of all formula arguments. NAME will be interpreted the same as Name.

Topics

A topic is a source of streaming data. It can be one of the following values:

• “Security” where
  <key> = the code of the stock item
  <field> = one of the available field names
• “Order” where
  <key> = the order number
  <field> = one of the available field names
• “DepthLevel” where
  <key> = the code of the stock item
  <field> = one of the available field names
  <index> = the level number in the depth
• “TradingAccount” where <key> = the code of the trading account
  <field> = one of the available field names

Examples

    =RTD("MotifXL.RTDServer",,"Security","4022.MYX","Name")
    =RTD("MotifXL.RTDServer",,"DepthLevel",B7,"BidPrice","1")
    =RTD("MotifXL.RTDServer",,"Order","20220701725","Status")

Literal vs. Cell reference

The values passed into the RTD statement can be either literals, as in the case of “4022.MYX”, or references to cells which contain the appropriate value for lookup.

Complex fields

Some fields presented through the various topics are lists or sets of data. For example, in the “Security” topic there is a field named “AlternateCodes” which contains all the known synonym codes for the item. This data is a list of codes and their values, and is displayed in text like “ISIN=MYL4022OO009, Ticker=TADMAX”. With these types of fields it is possible to refine the display to a particular item of the list by providing the item name in the field selector like “AlternateCodes.ISIN”. Likewise for sets of data like “CategoryCodes” we can specify the flag and the result displayed will be True/False.

Complex fields are denoted by an asterisk against the field name in the relevant RTD Builder.

RTD formula builders

A quick way to set up RTD formulas is to click one of the buttons under Streaming from the “MotifXL” ribbon bar. Each of these buttons will display a tool panel to help create a set of RTD statement that can be injected into the current spreadsheet.

For example, the “Add Securities” panel can be used in the following manner:

1. Typing one or more security codes into the “Symbol(s)” control. If more than one is entered make sure they are separated by commas (,).

2. Select from which Market data should be obtained. (Applies to all specified securities).

3. Select for which fields RTD formulas should be created. ("Select All" and “Select None” buttons can assist with this.)

4. Optionally, select whether a row of headings should be included above RTD formulas.

5. In the spreadsheet, select the top left most cell where RTD formulas (or headings) should start from.

6. Click Track in the “Add Securities” panel.

RTD formulas for the selected securities will be set up in the spreadsheet. If you are logged in, they should immediately begin retrieving data from the servers and begin updating the spreadsheet cells.

Using the RTD builders feature is a quick way to understanding how RTD formulas are set up, and which fields are available for each topic. You can use it initially to view the format and then create your own accordingly.

RTD constructor

An alternative method of building RTD formulas is to use the “RTD Constructor” in the Reference group of the “MotifXL” ribbon bar. This tool will allow you to build an individual cell formula from all the available topics and fields, including complex fields. Simply choose the Topic, supply a value or cell reference for the Key, then choose the Field. Optionally choose a Sub value (for complex fields), or an Index if required by the topic. You will see the RTD formula being built in the control. You can copy the formula, or paste it into the active cell on the sheet. If you change the active cell to one with an RTD formula, it will try and interpret the formula and display the values in the selectors.

Learn VBA

In order to use IQ API within Excel you need to understand Excel VBA (Visual Basic for Applications). VBA is the name of the programming language inside Excel (and other Microsoft Office products).

VBA is accessed via the Developer menu item in Excel. However, since most Excel users do not need to use VBA, this menu item is hidden by default. To display the Developer menu item, follow the corresponding steps in the Installation section of this manual.

If you have not previously used VBA inside Excel, there are numerous books and internet websites which can quickly teach Excel VBA. It is important to gain this understanding before proceeding onto the IQ section of this manual. A good tutorial on Excel VBA is available at:
https://www.excel-easy.com/vba.html

If you can understand that, then you are ready to use IQ inside Excel.

IQ

MotifXL brings the full power of Motionite’s IQ API to Excel. And it also makes it extremely easy to use. An IQ statement can be executed with a macro of a few lines and the results easily displayed in a spreadsheet.

This manual will describe how to use the “MotifXL.IQServer” library which provides access to IQ capabilities. It will supply a large number of VBA snippets showing how IQ can be used inside Excel. However it will not describe the IQ API itself. The documentation for the IQ API is available under API services. Most likely though, the code snippets will demonstrate enough of the IQ API so that you understand it even without the API documentation.

IQ Functions

The IQ functions available are shown below:

CreateObject("MotifXL.IQServer") As Object

IQServer.IsActive() As Bool
IQServer.ExecuteIQCommand(Command As String) As Bool
IQServer.ExecuteIQCommandWithTimeout(Command As String, TimeoutSeconds As Long)
IQServer.GetFailureMessage() As String
IQServer.GetJSON() As String
IQServer.GetColumnCount() As Long
IQServer.GetRowCount() As Long
IQServer.GetColumnName(ColumnIndex As Long) As String
IQServer.GetColumnType(ColumnIndex As Long) As String
IQServer.GetColumnSubType(ColumnIndex As Long) As String
IQServer.GetFieldByName(RowIndex As Long, ColumnName As String) As Variant
IQServer.GetFieldByIndex(RowIndex As Long, ColumnIndex As Long) As Variant
IQServer.Clear()
IQServer.GetIQAuthorizationHeader() As String
IQServer.GetIQServerUrl() As String

CreateObject(“MotifXL.IQServer”)

The CreateObject() function is used to create an internal IQServer object inside Excel. This IQServer object will:

1. execute an IQ statement on the server connected to when MotifXL logged in
2. retrieve the result set from the server.
3. make the data available via several functions.

This is illustrated in the example below:

Set IQ = CreateObject("MotifXL.IQServer")
Command = "select * from security where SymbolCode = '1015.MYX[Demo]'"
IQ.ExecuteIQCommand (Command)
ColCount = IQ.GetColCount()

The “IQServer” object can be reused with successive IQ commands. Each IQ command will delete the results from the previous IQ command execution.

When “IQServer” is no longer required it can be explicitly set to “nothing” to ensure the last result set is cleared from memory.

Note that the rest of the functions listed above are methods of the “IQServer” object/class.

IsActive()

IQServer.IsActive() As Bool

Indicates whether MotifXL is logged in.

Returns:

• True if MotifXL is logged in.
• False if MotifXL is not logged in.

ExecuteIQCommand(Command)

IQServer.ExecuteIQCommand(Command As String) As Bool

Executes the IQ Command specified in the Command parameter. The result from the command is stored in IQServer where it can be queried with other IQServer methods.

Uses a default timeout of 15 seconds.

Returns:

• True if command was successfully executed.
• False, if an error occurred.

ExecuteIQCommandWithTimeout(Command, TimeoutSeconds)

IQServer.ExecuteIQCommandWithTimeout(Command As String, TimeoutSeconds As Long)

Executes the IQ Command specified in the Command parameter. The result from the command is stored in IQServer where it can be queried with other IQServer methods.

The TimeoutSeconds parameter specifies the timeout in seconds.

Returns:

• True if command was successfully executed.
• False, if an error occurred.

GetFailureMessage()

IQServer.GetFailureMessage() As String

Returns the failure message if an error occurred when an IQ command was executed.

GetJSON()

IQServer.GetJSON() As String

Returns the JSON string returned by an IQ command. The IQ API returns all result sets from IQ commands as a JSON string. This method returns that JSON string.

Use this method if you wish to parse the Result Set yourself instead of using the IQServer functions to parse the Result Set.

GetColumnCount()

IQServer.GetColumnCount() As Long

Returns the number of columns in a Result Set.

GetRowCount()

IQServer.GetRowCount() As Long

Returns the number of rows in a Result Set.

GetColumnName(ColumnIndex)

IQServer.GetColumnName(ColumnIndex As Long) As String

Returns the name of the column at the position specified by the ColumnIndex parameter in a Result Set.

GetColumnType(ColumnIndex)

IQServer.GetColumnType(ColumnIndex As Long) As String

Returns the variant type of a column. This corresponds to the field types specified in the IQ API documentation except enumeration types are specified as string.

• https://iq-cte.em2.asia/appendices/fields
• https://iq-cte.em2.asia/appendices/inserts

(Refer to GetColumnSubType() for enumeration type information)

The actual value returned will be the .NET name of the variant type. It will be one of:

• string
• boolean
• int32
• int64
• decimal
• datetime
• double

GetColumnSubType(ColumnIndex)

IQServer.GetColumnSubType(ColumnIndex As Long) As String

Returns the enumeration type of columns that contain an enumeration value. Note that the Column Type for these columns will always be string.

The types will be one of the enumerations types specified in the IQ API documentation: https://iq-cte.em2.asia/appendices

GetFieldByName(RowIndex, ColumnName)

IQServer.GetFieldByName(RowIndex As Long, ColumnName As String) As Variant

Returns a single value from the Result Set as a variant.

• The RowIndex parameter specifies the value’s row by index. Rows are numbered from 1.
• The ColumnName parameter specifies value’s column by its name. A column’s name can be determined with the GetColumnName() function.

GetFieldByIndex(RowIndex, ColumnIndex)

IQServer.GetFieldByIndex(RowIndex As Long, ColumnIndex As Long) As Variant

Returns a single value from the Result Set as a variant.

• The RowIndex parameter specifies the value’s row by index. Rows are numbered from 1.
• The ColumnIndex parameter specifies value’s column by index. Columns are numbered from 1.

Clear()

IQServer.Clear()

Clears the current Result Set from IQServer.

GetIQAuthorizationHeader()

IQServer.GetIQAuthorizationHeader() As String

Returns the Authorisation Request HTTP header used by MotifXL to access the IQ service. This header contains the Access Token needed to authorise an IQ web service request.

Use GetIQAuthorizationHeader() if you wish to make your own web service calls to the IQ service instead of using ExecuteIQCommand() or ExecuteIQCommandWithTimeout().

Do not cache the Authorisation Request HTTP header returned by GetIQAuthorizationHeader(). Since Access Tokens need to be refreshed, the header will change.

GetIQServerUrl()

IQServer.GetIQServerUrl() As String

Returns the webservice endpoint of the IQ Service.

Use GetIQServerUrl() if you wish to make your own web service calls to the IQ service instead of using ExecuteIQCommand() or ExecuteIQCommandWithTimeout().

This endpoint can be cached for the duration of the MotifXL login session.