Excel - capturing the raw data elements

[jimibt]

Morning all - first off, i have to be totally honest here and say that I've only superficially looked at the Excel integration and may have missed something. But anyway, here's my question.

Having looked at the Excel integration, I can see that it's using a DDE channel in order to populate the sheet at given cell references. This would imply that somewhere under the surface, the raw data is flowing and can thus be captured in a more purpose built structure. I would like to capture the channel data and use a number of class objects to store the data. My class object (at the simplest level), would be a collection of the runners and their prices/vwap etc. Each second, a new instance of the collection class would be created, with the fresh data, and added to a stack that would be used by a controller class to perfrom a variety of what-if's.

My question - how do i capture the underlaying data and populate the class objects i describe above. I'm not particularly interested in reviewing alternative solutions for now and would like to get a handle on the raw data as a starting point. So far, i've looked at both the Worksheet_Change and WorkBook_SheetSelectionChange events to no avail...

As an aside, when using the API in .net, I've built something similar and it works well as it allows me to examine the data offline and replay the markets at variable speeds in order to test different strategies. I need to refactor this mechanism down to vba in order to collaborate on something plus allow BA to handle the solid transactional implementation.

hope this makes sense...

[foxwood]

Maybe BA support can give some info to help ?

I'd have a look at writing a fake DDE client to replace / tee the Excel connection - old ddespy.exe allows monitoring which is what a tee would do - source code was part of SDK for one of the old versions of Visual Studio

[jimibt]

Maybe BA support can give some info to help ?

I'd have a look at writing a fake DDE client to replace / tee the Excel connection - old ddespy.exe allows monitoring which is what a tee would do - source code was part of SDK for one of the old versions of Visual Studio

thanks for the suggestion, however, as much as possible, i'd like to keep the moving parts confined to those supplied *out of the box* where possible using BA->Excel. this will allow vba classes to be added to the mix as required (don't get me started on implementing .net dll's for excel consumption!! - too many days lost in the ether getting those to work in the past [tho it can/does work -EVENTUALLY]).

btw- I think DDESPY only works on 32 bit systems and Spy++ doesn't seem to work either, so will be relying (i think) on the experience of others on here that have trodden this path - not laziness, just pragmatism

[spreadbetting]

As an aside, when using the API in .net, I've built something similar and it works well as it allows me to examine the data offline and replay the markets at variable speeds in order to test different strategies. I need to refactor this mechanism down to vba in order to collaborate on something plus allow BA to handle the solid transactional implementation.

hope this makes sense...

If you have a .net already connecting to the API why not simply have that handling all the transactions? I'm not sure why you'd want to add in extra layers of hassle if you're already somewhat competent in .net especially as the API is relatively simple for bet placement/retreival etc

[jimibt]

As an aside, when using the API in .net, I've built something similar and it works well as it allows me to examine the data offline and replay the markets at variable speeds in order to test different strategies. I need to refactor this mechanism down to vba in order to collaborate on something plus allow BA to handle the solid transactional implementation.

hope this makes sense...

If you have a .net already connecting to the API why not simply have that handling all the transactions? I'm not sure why you'd want to add in extra layers of hassle if you're already somewhat competent in .net especially as the API is relatively simple for bet placement/retreival etc

hi SB, this route is due to it being a collaborative venture with a few other individuals who will be majoring on the exel based stuff (and these guys don't have developer API keys with BF - if it were a solo project, then this would be a non issue). my main input to the project will be to create the data structures and playback model. also, guardian and the BA engine offer a far more robust transaction model and thus saves quite a bit of dev overhead.