After a lot of messing about I decided to start again with the data collecting code. I’d got to a point where there was so much going on with the array that I was hitting error after error. So I thought a fresh start was a good plan and go back to my first idea of just collecting the data for later analysis.
As mentioned previously there is a lot of data to collect. My solution is to create and name a new workSheet after a new event is selected, then record the data for that event to it’s own sheet. I wanted the sheet name to be relevant to me and as there is a limit to the number of characters that can be used, I chose the event start time as the sheet name. I grab this from the first cell as passed to excel. To prevent an error if there are two events with the same start time, my code checks to see if the sheet already exists before creating it. At the end of each day the event sheets are saved as a separate file and deleted from the bot, ready for the next day’s trading.
Another problem I came up against was how to paste an array to a sheet that isn’t active. I don’t want to activate a sheet, paste data then activate the main sheet as this is very slow. After some googling I found that, for example, .range(Cells(1, 1), Cells(10, 10)) is actually read as .range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(10, 10)). So I replaced the “ActiveSheet” bit with the sheet name I wanted. It works.
I want to do some more testing before I make this live as I’ve only tested on some Betdaq markets.
I’m also planning on adding a page to the blog to put code examples for different problems as I spend too much time searching for things I’ve used before but can’t remember. It might help others too.