Excel Integration

Marketcetera now provides a library for use with pricing models and other algorithms implemented in an Excel spreadsheet. By including a reference to the MarketceteraCOM Component, you can now:

• Start a connection to the Marketcetera platform
• Send new orders (in the FIX format)
• Send cancel messages (in the FIX format)
• Send cancel/replace messages (in the FIX format)
• Generate globally unique order ids
• Notify the client of execution reports asynchronously through an event
• Notify the client of any application-level error conditions (FIX messages)
• Notify the client of any error conditions on the network connection asynchronously through an event

This page defines the IMarketceteraAdapter, which is intended for use in Excel Spreadsheets, and other locations where simple message sending semantics are required. For more expressive message construction, and better handling of response messages, see the .Net/COM API.

System Requirements

The MarketceteraCOM has the following requirements:

• Windows XP SP2 or later
• Microsoft Office 2003 or later

As part of the installation process, version 2.0 of the .Net runtime may be installed, along with some required Microsoft Knowledge Base patches.

Installation

Installation is quick and painless.

• Download the MarketceteraCOM component from the download page.
• Extract the installer from the zip archive
• Double-click "Marketcetera.msi" in the top level folder, and answer the prompts in the installation wizard.

The MarketceteraCOM library should now be available to all Excel spreadsheets. You simply need to add a reference to the library inside of the spreadsheet that would like to use it. To do this:

• Open the spreadsheet that you would like to use the MarketceteraCOM plugin.
• From the Tools menu, choose Macro->Visual Basic Editor
• In the Visual Basic editor window, choose Tools->References
• Make sure the entry for the MarketceteraCOM Component and Microsoft Scripting Runtime are checked.

That's it.

Examples

We have a sample excel document that allows you sending limit and market orders to the OMS. We are not particularly proud of the VB (but hey, that's why we implemented Ruby scripting in Photon instead).

• Be sure to point it to a valid instance of Marketcetera OMS
• Hit the "Start" button
• Limit and Market orders work, but Cancels and Cancel Replace do not work very well because we don't have the Excel spreadsheet capturing the OrderIDs assigned by the exchange.
• The "extra fields" are for any additional fields you need to be set

This sample is not meant to be a real trading spreadsheet but only as a basic showcase of how to use Excel integration.

API Reference

Note that this API has changed as of version 0.4.0 of the Marketcetera platform. IMarketceteraAdapter provides a "simple" API, in that you need not construct a message object prior to sending it, you simply need to call the appropriate "Send..." method.

using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Runtime.InteropServices;

namespace MarketceteraCOM
{
    public delegate void FIXStringMessageHandler(String message);
    public delegate void ExceptionStringHandler(String ex);

    public interface IMarketceteraAdapter
    {
        /// <summary>
        /// Initializes the connection to the Marketcetera platform.
        /// </summary>
        /// <param name="serverURL">The URL of the ActiveMQ connection</param>
        /// <param name="destinationQueue">The name of the queue to which to send messages</param>
        /// <param name="responseTopic">The name of the topic from which to receive messages</param>
        /// <param name="fixVersionString">The version of FIX to use for this connection.  
        /// Currently the only supported version is "FIX.4.2", all other values will cause an exception.</param>
        void Init(String serverURL, String destinationQueue, String responseTopic, String fixVersionString);

        /// <summary>
        /// Start the adapter, making an network connection to the message queue.
        /// </summary>
        void Start();

        /// <summary>
        /// Stop the adapter, disconnecting from the message queue and freeing all resources.
        /// </summary>
        void Stop();

        /// <summary>
        /// Sends a new limit order (MsgType = D) over the FIX connction, returning a ClOrdID for the current order.
        /// This method will fill in the required field HandlInst with '3' (manual order), the required field
        /// OrdType with '2' (limit), and the required field ClOrdID with a generated unique identifier.
        /// 
        /// This method will throw an exception if the connection has not been started with the Start() method.
        /// </summary>
        /// <param name="symbol">The Symbol field for the order</param>
        /// <param name="side">The Side field, should be one of the allowed values of the Side FIX field</param>
        /// <param name="quantity">The OrderQty field as a Decimal</param>
        /// <param name="price">The Price field as a Decimal</param>
        /// <param name="extraFields">A dictionary of extra fields to add to the order, with keys being integers representing
        /// the fix field numbers and values being string representations of the field value.</param>
        /// <returns>A unique identifier that was sent as the ClOrdID field</returns>
        String SendLimitOrder(String symbol, short side, Decimal quantity, Decimal price, Scripting.Dictionary extraFields);


        /// <summary>
        /// Sends a new limit order (MsgType = D) over the FIX connction, returning a ClOrdID for the current order.
        /// This method will fill in the required field HandlInst with '3' (manual order), the required field
        /// OrdType with '1' (market), and the required field ClOrdID with a generated unique identifier.
        /// 
        /// This method will throw an exception if the connection has not been started with the Start() method.
        /// </summary>
        /// <param name="symbol">The Symbol field for the order</param>
        /// <param name="side">The Side field, should be one of the allowed values of the Side FIX field</param>
        /// <param name="quantity">The OrderQty field as a Decimal</param>
        /// <param name="extraFields">A dictionary of extra fields to add to the order, with keys being integers representing
        /// the fix field numbers and values being string representations of the field value.</param>
        /// <returns>A unique identifier that was sent as the ClOrdID field</returns>
        String SendMarketOrder(String symbol, short side, Decimal quantity, Scripting.Dictionary extraFields);

        /// <summary>
        /// Sends a cancel request for the order specified by origClOrdId, returning a new identifier for this cancel
        /// message.
        /// 
        /// This method will throw an exception if the connection has not been started with the Start() method.
        /// </summary>
        /// <param name="origClOrdId">The ClOrdID of the order to cancel (usually returned by 
        /// SendLimitOrder or SendMarketOrder</param>
        /// <param name="symbol"></param>
        /// <param name="side">The Side field, should be one of the allowed values of the Side FIX field</param>
        /// <param name="transactTime">The creation time of the original order. Can be found in the
        /// ExecutionReport acknowledging the order</param>
        /// <param name="extraFields">A dictionary of extra fields to add to the order, with keys being integers representing
        /// the fix field numbers and values being string representations of the field value.</param>
        /// <returns>A unique identifier that was sent as the ClOrdID field</returns>
        String SendOrderCancel(String origClOrdId, String symbol, short side, DateTime transactTime, Scripting.Dictionary extraFields);

        /// <summary>
        /// This method will send a new cancel/replace request (MsgType = G), returning a unique identifier for this
        /// request.
        /// 
        /// This method will throw an exception if the connection has not been started with the Start() method.
        /// </summary>
        /// <param name="origClOrdId">The ClOrdID of the order to cancel (usually returned by 
        /// SendLimitOrder or SendMarketOrder</param>
        /// <param name="symbol"></param>
        /// <param name="side">The Side field, should be one of the allowed values of the Side FIX field</param>
        /// <param name="transactTime">The creation time of the original order. Can be found in the
        /// ExecutionReport acknowledging the order</param>
        /// <param name="ordType">The order type for the resulting new order, should be one of the values allowed for the OrdType FIX field</param>
        /// <param name="extraFields">A dictionary of extra fields to add to the order, with keys being integers representing
        /// the fix field numbers and values being string representations of the field value.</param>
        /// <returns>A unique identifier that was sent as the ClOrdID field</returns>
        /// <returns></returns>
        String SendOrderCancelReplace(String origClOrdId, String symbol, short side, DateTime transactTime, short ordType,
            Scripting.Dictionary extraFields);

        /// <summary>
        /// This is a generic method that can be used to send any type of message, specifying separately the fields
        /// to go into the FIX message header, body and trailer.
        /// 
        /// This method will throw an exception if the connection has not been started with the Start() method.
        /// </summary>
        /// <param name="headerFields">The fields to go into the FIX header</param>
        /// <param name="bodyFields">The fields to go into the main body of the FIX message</param>
        /// <param name="trailerFields">The fields to go into the FIX trailer</param>
        /// <returns></returns>
        String SendMessage(Scripting.Dictionary headerFields, Scripting.Dictionary bodyFields,
            Scripting.Dictionary trailerFields);

        /// <summary>
        /// Event that notifies the client of "application level" events, as defined by the QuickFIX API.  These
        /// include execution reports and cancel rejects.
        /// </summary>
        event FIXStringMessageHandler ApplicationMessageEvent;

        /// <summary>
        /// Event that notifies the client of "session level" events, as defined by the QuickFIX API.  These
        /// include login, logout, and rejects for malformatted messages.
        /// </summary>
        event FIXStringMessageHandler SessionMessageEvent;

        /// <summary>
        /// Event that notifies the client of any other asynchronous error condition.
        /// </summary>
        event ExceptionStringHandler ExceptionEvent;
    }

    [InterfaceTypeAttribute(ComInterfaceType.InterfaceIsIDispatch)]
    public interface IMarketceteraAdapterEvents
    {
        void ApplicationMessageEvent(String message);
        void SessionMessageEvent(String message);
        void ExceptionHandler(String message);
    }
}