Recording data

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.


2 thoughts on “Recording data”

  1. Do you do all this cool things in Excel?

    You can use something like this too:
    Sheets(“target_sheet”).Range(“A1:J10”).Value = Sheets(“source_sheet”).Range(“A1:J10”).Value

    I’m trying develop some automated thing for fun and started with a bit of code in PHP, storing all the data in a MySQL database…
    But I don’t know what do with all this data… lol.
    I’m studying, thinking and talking with some people trying to realize what to do with the data.

    Keep writing… your blog is interesting!


    1. Hi Bruno,

      Yes I write this in excel VBA. I don’t know PHP but have some HTML knowledge. I’m also starting to develop a bot in so i don’t need to use excel and gruss.

      The range needs to be dynamic to allow for any number of runners in an event, which is why I find it easier to use the ‘cells’ method.

      Also, to speed up the code, all the data is collected into an array first, then all calculations are done, any instructions are added, then the array is pasted back over it’s original position. It was this array that I was trying to paste in another sheet. I finally got it working.

      Thanks for your support


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s