Hi

Bit of a VBA newbie and I need some help. I have a Betfair API program
which can connect with Excel. Their default worksheet (Sheet(1))
displays the runners names and various numerical data, such as current
odds, market volumes etc. I want to record all the data for each
runner at one second intervals and write it to a separate worksheet
for each runner so that I can chart it after the event for analysis.

So far I have managed to write code to establish the number of runners
(1-N), create a sheet for each and rename it as per the runners names,
format it, insert column headings for the data and then link the
current numerical data for that runner in range A1:Y1. It probably
isnt the most efficient code but it works!

Now I need to code to write the current numerical data in range A1:Y1
row by row (A2:Y2, A3:Y3, etc) at one second intervals for each
runners worksheet. I think this would be best achieved with an array
to read and write the data and a loop of some kind combined with a
timer function set to fire at one second intervals. The loop needs to
ignore Sheets(1) and loop through Sheets (2-N). The timer needs to
start as soon as the worksheets 2-N are up and running. Id like the
timer to be killed off programmatically but I cant find a suitable
indicator as yet so I think that would have to be done manually.

I really need help with this bit as its the whole point of the
exercise and Im afraid to write code that lands me in an endless loop
or only loops on one runner.

Look forward to your responses.

Use ONtime to scedule the timer event. See VBA help ONtime.

Note: the VBA help says to set schedule to false to stop timer

This example cancels the OnTime setting from the previous example.

Application.OnTime EarliestTime:=TimeValue("17:00:00"), _
Procedu="my_Procedure", Schedule:=False


I would split the runners times to seperate worksheets after the event
finishes. Use AutoFilter to get each runners data row from sheet 1. the
use special cells method to copy the VISIBLE cells to each worksheet.

If the sheet name is the runner name in column A of sheet 1

for ShtCount = 2 to sheets.count
with sheets(ShtCount)
Runner = .name
with Worksheets("Sheet1")
.Range("A1").AutoFilter _
field:=1, _
Criteria1:=Runner
VisibleDropDown:=False

.Cells.SpecialCells(xlCellTypeVisible).copy
end with

.cells.paste

end with


next ShtCount




"FinnBarr" wrote:

Hi

Bit of a VBA newbie and I need some help. I have a Betfair API program
which can connect with Excel. Their default worksheet (Sheet(1))
displays the runners names and various numerical data, such as current
odds, market volumes etc. I want to record all the data for each
runner at one second intervals and write it to a separate worksheet
for each runner so that I can chart it after the event for analysis.

So far I have managed to write code to establish the number of runners
(1-N), create a sheet for each and rename it as per the runners names,
format it, insert column headings for the data and then link the
current numerical data for that runner in range A1:Y1. It probably
isnt the most efficient code but it works!

Now I need to code to write the current numerical data in range A1:Y1
row by row (A2:Y2, A3:Y3, etc) at one second intervals for each
runners worksheet. I think this would be best achieved with an array
to read and write the data and a loop of some kind combined with a
timer function set to fire at one second intervals. The loop needs to
ignore Sheets(1) and loop through Sheets (2-N). The timer needs to
start as soon as the worksheets 2-N are up and running. Id like the
timer to be killed off programmatically but I cant find a suitable
indicator as yet so I think that would have to be done manually.

I really need help with this bit as its the whole point of the
exercise and Im afraid to write code that lands me in an endless loop
or only loops on one runner.

Look forward to your responses.

Joel

Thanks for your response.

I tried the OnTime & a Windows API timer procedure, both of which I
found on Chip Pearsons webpage. The Windows API fell over after a
while as he predicted. The OnTime, which I lifted straight from the
webpage, makes one call and stops when run from the VBE and makes no
calls when assigned to a menu item. As the Windows timer worked fine
for 20 mins before crashing I know the rest of my code is ok. Cant
work out what the problem is. Ive left another post entitled Timer
Procedures with further details including a condensed version of the
code.

Regarding your suggestion to split the data after it has been
recorded, I had considered this initially but discounted it. Once I
have the data recorded I intend to chart it and perform some
statistical analysis on it to enable me to identify those factors
which indicate price movements, for example, exponential weighted
averages, standard deviations, bollinger bands, weight of money,
correlation, covariance etc. Once I have identified these variables, I
intend to write further procedures which analyse the data in real time
and flag up potential situations to trade on, and subsequently, to
automate the trading process. If my code doesnt split the data until
after the event, the market will be closed and I cant trade it.
Alternatively, I could code a solution that loops through the data on
a step value. If there are, say, 10 runners in a race, Id have to step
1, 11, 21, etc for the first in the market, 2, 12, 22, etc for the
second and so on. As Im not that au fait with VBA (this is my first
excursion into programming!), I think that this approach would exceed
my capabilities. I know my code works ok, except for the timer, but Im
sure its not a very elegant way of achieving it. No doubt VBA experts
could have done twice as much with half the code and in a fraction of
the time! My approach in overcoming the data split straight off the
bat should make subsequent coding simpler for me. Thats the theory,
anyway!

GG