Building a Trading Application using Excel DNA

[Sebastian Stuecker]

Hi,

I am new to excel dna and also I consider myself a beginner to c# and .NET. I spent a few days reading tons of stuff about .NET though.

I am posting here not to get detailed explanations of what I have to do, but simply a short feedback if you beleive I am on the right path using Excel DNA and .NET in the first place for what I want so that I can dive in deeper and read what is needed to get an understanding of everything.

I want to build a trading application in Excel. My Broker offers a REST API and a LightStreamer API for live stock data or live changes to running positions.

Main features required:

- display time-series based OHLC data of specific stock symbols (e.g. the 25 last 1 hour candles). Such a list I get through REST. 

- I want to add manually data in additional columns which in the future I might receive from another data source. This can be pure excel, and I will use this data together with the OHLC data to calculate possible buy levels, stop loss levels etc.

- I want a button or similar on that sheet to fire the precalculated order to the broker through another REST call. Alternatively I want to be able in the long run to even do this automatically based on values in the cells.

- Real time price data shall be added from the Lightstreamer Service and I was thinking of doing this with RTD, so implement an RTD server in the xll.

- I want to have features to track trades in a local database or excel sheet to start with for statistics etc.

- other functions like changing pending orders or canceling them

All in all there is going to be a mix of data received from the broker and entered manually and based on that there is data processing happening that will trigger calls being sent back to the broker or data being stored in maybe an external database.

My simple Question: Is this the right technology? What should I read. What are some keywords I should be interested in. 

The broker already provides a C# implementation of the REST API client and also of the Lightstreamer Client. 

Sorry if this is in the completely wrong group. 

Thanks!

[Sebastian Stuecker]

Just a short update, I played around a lot with ExcelDNA the last days and I beleive now that it is the right technology for my needs. I have found a lot of stuff in this group here to help me already.

There is one topic I cant seem to find the best way, and that is, from inside a UDF function, get the name of the sheet the UDF function was called. 

Another item I am not sure about yet is, if I should use a technique to fill ranges with data actively with a macro or even from inside a UDF function even if this is against the excel way of cell interaction, or if I should try to map every cell value to a UDF so that on a Sheet I might have a couple of hundred UDF calls. 

Same is for RTD, I am wondering how many I should use performance wise. I have not tried it yet, but I guess i need to register new RTD objects based on a custom RTD-Server class deriving from ExcelRtdServer in a dynamic way. 

Example: Lets say in one cell I want to have th current ask price for a stock, I would call something like =dnaRtdStockPriceStreamer(datasource;stocksymbol) and my backend would get the right data from the right data provider (I want to support data from several providers)

Something else I still need to solve: I have a running ViewModel in the background (as I reused a lot of code from a WPF sample app from my broker) where the stocks are represented as objects and they are subscribed to a streaming server (Lightstreamer) where their data is updated regularly based on data coming from the streaming server, and that is done doing INotifyPropertyChanged. I somehow need to trigger on those change events to also update the values through RTD.

This is just for sharing with you but if anyone things what I write is a completely wrong approach I would be happy for any feedback.

Regards

Sebastian

[Govert van Drimmelen]

Hi Sebastian,

For the calling sheet name you can use a combination of the xlfCaller and xlSheetNm C API calls:

    [ExcelFunction]

    public static string GetCallingSheetName()

    {

        ExcelReference caller = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);

        string sheetName = (string)XlCall.Excel(XlCall.xlSheetNm, caller);

        return sheetName;

    }

Equivalently, with some of the C# 6 features like "using static ExcelDna.Integration.XlCall" you might write the function as:

    [ExcelFunction]

    public static object GetCallingSheetName() => Excel( xlSheetNm, Excel(xlfCaller) );

I would recommend making single-cell worksheet functions where possible. If you're making calls to a back-end database or web service, you might use the async batching pattern to gather many such calls into a single back-end batch. There's an example of how to implement the async batch pattern here: https://github.com/Excel-DNA/Samples/blob/master/Misc/AsyncBatchSample.cs 

Having millions of UDF calls from a workbook should not be a problem.

Regarding RTD, you can either implement your own RTD server by deriving from the ExcelRtdServer base class, or go for the higher-level IObservable based approach. This uses an RTD server implementation that is built into Excel-DNA to provide an abstraction over RTD that matches the Reactive Extensions library IObservable / IObserver interfaces.

The RtdClocks sample projects show how to implement a ticking clock function using increasing these different levels of abstraction: https://github.com/Excel-DNA/Samples/tree/master/RtdClocks

You could use a similar approach to wrap INotifyPropertyChanged into an IObservable event stream that you make available through a UDF function.

-Govert