Force update on excel spreeadsheet

[summit]

Hi all,

I wonder if someone can help me please.

I have composed a spreadsheet that works fine. However, the whole premise that I have is based on updating the results.

Currently, the results are updating fine, but ONLY when the next bet is triggered. This is too late for the stake on that bet to be re-calculated. I therefore need the results to update from Betfair onto my spreadsheet earlier so that the bet stake can be recalculated BEFORE the next bet.

Is there any way to force a RESULT UPDATE into my spreadsheet?

Thanks for any help

Steven

[Captain Sensible]

You can force the bet results sheet to update by putting -6 in Q2.

[summit]

That's great, thank you...

But is there a way to automate putting -6 in q2.

With my limited knowledge of excel, every formula that I put in works fine and updates, but then disappears as q2 refreshes.

Any ideas?

Thanks a lot

[Captain Sensible]

Probably best using some VBA to stick the -6 in Q2 as the cell clears on each refresh so it doesn't keep duplicating the special triggers.

VBA isn't as hard as it appears, plenty of examples on the forum. It's a bit hard to give an example as I don't use the bet results sheets so no idea when you'd want the sheet updating, when you enter a new market , a few seconds before the race starts etc All depends when you want to place the bets and what you'd want to do if the previous race result wasn't in yet i.e. stewards enquiry , delay etc

[summit]

Hi All,

Well, I looked on youtube and google and, after much headscratching, managed to put my first VBA code to work. I have only tested it for an hour or so on my system but I am pretty sure it will continue to work. It creates a macro called Gruss2.

The following VBA puts -6 in cell Q2, forcing the update for the results and U in J2, forcing the balance of your Betfair account to update.

Hope it is of use to some.

Dim TimeToRun
Sub auto_open()
Call ScheduleGruss2
End Sub
Sub ScheduleGruss2()
TimeToRun = Now + TimeValue("00:00:30")
Application.OnTime TimeToRun, "Gruss2"
End Sub

Sub Gruss2()
Range("Q2").Select
ActiveCell.FormulaR1C1 = "-6"
Range("J2").Select
ActiveCell.FormulaR1C1 = "U"
Range("A11").Select
Call ScheduleGruss2
End Sub
Sub auto_close()
Gruss2
Application.OnTime TimeToRun, "Gruss2", , False
End Sub



All the best
Steven

[summit]

Sorry All,

There seems to be a problem with this code, I will try and sort and get back to you.

[pie2251]

This Is What I Discovered, After A Lot Of Stuffing Around.
Short And Simple.

Add This To Sheet1 Code.

Private Sub worksheet_change(ByVal target As Range)
If target.Columns.Count = 16 Then
Range("Q2").Value = -6
End If

End Sub

[Captain Sensible]

Good to see you sorted it, only problem I can see is that you'd be calling that data on each refresh so not really that efficient. I'm not sure where Gary requests that data from but I'd guess it's calling the account statement and Betfair don't usually like you hammering that data too often so certain calls do get restricted by Betfair.

As far as I'm aware BA makes a request for the results data each time it loads a market so if your current spreadsheet fires straight away you won't actually gain any advantage, if your sheet fires a set time before a race start etc it might be worth you setting your code to update the results when it's actually needed rather than each refresh.