Excel DNA - integration of .net in excel

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
User avatar
jimibt
Posts: 3641
Joined: Mon Nov 30, 2015 6:42 pm
Location: Narnia

Just thought i'd share this as this literally opens the door up for anyone that codes in .net but has ambitions to code their strategies via excel/vba.

For me, altho i don't find vba offputting, it can at times be a duplication of effort as i tend to prototype stuff in c# and then try and refactor it into vba thereafter (to marry up with the BA spreadsheet). I got a bit frustrated by this and took a walk down the native .net/com dll integration and life got sweeter. I thought i'd found a mini nirvanna doing this, until i stumbled upon this: https://excel-dna.net/

Excel-DNA is a free/indie development. The blurb states:

Excel-DNA is an independent project to integrate .NET into Excel. With Excel-DNA you can make native (.xll) add-ins for Excel using C#, Visual Basic.NET or F#, providing high-performance user-defined functions (UDFs), custom ribbon interfaces and more. Your entire add-in can be packed into a single .xll file requiring no installation or registration.

You can use this library and com interop as a way to port your .net code over to Excel to be used as inline worksheet functions (formulas), as well as allowing the.net libraries to be used inside the vba shell as if native classes.

So in effect, combining the com interface and the Excel-DNA approaches means that you can use all your .net goodness (linq, stacks etc) and use vba purely as an orchestrator.

anyway, hope this opens the doors for anyone else out there that has mainly expertise in .net and would like to extend it out to BA/Excel integration.

[see also]
https://stackoverflow.com/questions/199 ... d-from-vba
https://stackoverflow.com/questions/370 ... -excel-vba
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

Thanks for that, it looks interesting. Now I have to figure out what can I drop from my todo list so I can look at it.
User avatar
jimibt
Posts: 3641
Joined: Mon Nov 30, 2015 6:42 pm
Location: Narnia

ShaunWhite wrote:
Wed Oct 24, 2018 1:38 pm
Thanks for that, it looks interesting. Now I have to figure out what can I drop from my todo list so I can look at it.
i think this has the power to change the way that the poor relation (vba) is viewed. i did a test on it last night and it worked surprisingly well. the fact that you can use the com interop route to target objects in vba and also the Excel-DNA approach to create user defined formulae (for on sheet manipulation) is pretty all encompassing. vba ends up just being an orchestrator/event sink for the best part. means you can use all the .net goodness such as linq and in short, your .net expertise.
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

Are these standalone programs or still relying on BA for price feeds, bet placement etc, jimibt?
User avatar
jimibt
Posts: 3641
Joined: Mon Nov 30, 2015 6:42 pm
Location: Narnia

spreadbetting wrote:
Wed Oct 24, 2018 2:14 pm
Are these standalone programs or still relying on BA for price feeds, bet placement etc, jimibt?
SB - this is all done using BA->Excel integration, so they piggyback the pricefeeds from BA. the betangel sheet is literally *touched* to pull the properties into an object structure and everything is done inside these dlls. the sheet is only referenced on the trigger refresh to pick up the range of properties on each iteration. Bet placement in BA is fulfilled via events raised by an observer class (the sheet only being touched to place these bets). this ensures that everything is encapsulated in a testable environment.

so in essence, the bet angel sheet is only ever touched to grab each new set of prices and then again when bets are being placed/cancelled.

this is part of the collaborative project that i mentioned a few weeks back. thus far, i'm pretty pleased that i was able to adapt and use this hybrid approach. on refreshes of 500-1000ms, there's no stress on the system. i haven't taken it below these resolutions yet but am confident that due to the light touch on the sheet, there would be little change in the memory/cpu footprint.

an additional side benefit is that the data can be saved, at the end, via Json.Net to an object structure that can be fed back into the *system* for replay at a later date in order to test strategy models (ToDo :D)

[edit] to boil it down, the structure is => base class objects, observer class and strategy class (which is closely tied to the observer class, but calls a different stategy implementation depending on the observer event cargo).
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

Thanks
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”