Automating quick pick list (daily refresh)

[nick257]

Hi guys,

I've implemented the VBA approach outlined here, to put "-3", the "-5" in the Market sheet, in cell Q2
http://www.gruss-software.co.uk/forum/v ... php?t=3558

The Market sheet is reliably updated by BA with Market data. However, the nightly process to populate cell Q2 fails to refresh/reload markets in BA.
I run the script on a timer at 07:00hrs, at 01:00hrs, and even tried manually typing "-3" in the Q2 cell, without success.

The various BA error logs are either 0KB, or offer no clues... except for "BA_Diagnostic-YYYY-MM-DD.log", for example:

On BA startup....

28072019 11:55:16:018: Get account details response: xxxxxxxxxxx
28072019 11:55:16:038: Connecting to Betfair order stream
28072019 11:55:16:050: Session currency:GBP
28072019 11:55:16:159: Sending authentication to order stream.
28072019 11:55:16:167: {"op":"connection","connectionId":"204-280719105514-227609"}
28072019 11:55:16:225: {"op":"status","id":1,"statusCode":"SUCCESS","connectionClosed":false}
28072019 11:55:16:225: Betfair order stream subscribed.
28072019 11:55:16:225: Betfair order stream connected
28072019 11:55:16:225: Subscribing to orders
28072019 11:55:17:486: Application version:1.3.0.43a
28072019 11:55:17:486: Adding news page
28072019 11:55:17:767: News tab page added.
28072019 11:55:18:937: Requesting Tennis scores available events.
28072019 11:55:19:093T0: addMarkets(). Market type:UK WIN
28072019 11:55:19:164T0: addMarkets(). Market type:IRE WIN


After I connect BA to Excel, following ActiveX error seen:

28072019 11:57:32:694: Searching for Excel instances using path:C:\Users\xxxxxxxxx\Gruss_Betting_Assistant\, based on path of found workbook:C:\Users\xxxxxxxxx\Gruss_Betting_Assistant\Place_Bets.xls
28072019 11:57:32:694: Excel instance found:Place_Bets.xls [Compatibility Mode] - Excel
28072019 11:57:32:698: Excel workbook found:Place_Bets.xls
28072019 11:57:32:698: Excel instance found:
28072019 11:57:32:698: Excel workbook found:
28072019 11:57:32:737: System.Exception: Cannot create ActiveX component.
at Microsoft.VisualBasic.Interaction.GetObject(String PathName, String Class)
at Betting_Assistant.ExcelOptions.populateWorkBooks(String openWBName)
28072019 11:57:32:737: System.Runtime.InteropServices.InvalidComObjectException: COM object that has been separated from its underlying RCW cannot be used.
at System.RuntimeType.InvokeDispMethod(String name, BindingFlags invokeAttr, Object target, Object[] args, Boolean[] byrefModifiers, Int32 culture, String[] namedParameters)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.VisualBasic.CompilerServices.VBBinder.InvokeMember(String name, BindingFlags invokeAttr, Type objType, IReflect objIReflect, Object target, Object[] args, String[] namedParameters)
at Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)
at Betting_Assistant.ExcelOptions.populateWorkBooks(String openWBName)
28072019 11:57:35:127: Excel logging activated.
28072019 11:57:35:127: Include pre-settled in results:False


Question: Would it be possible to get BA to log the name of the component it's failing to create please?


Thanks
Nick

[Captain Sensible]

Only refreshes the first BA tab and uses the market details from the Quick pick list you have set in preferences?

Is the sheet linked to the first tab in BA and also has markets set in preferences? What marets are you trying to load, Betfair are always slow at loading up new markets for things like greyhounds.

[nick257]

Thanks for the reply. I only bet on UK + IRE Win markets for horse racing, and only use a single tab in BA.

"Preferences" / "Quickpick list / markets" options I use as per screenshot.

[Captain Sensible]

When you say "even tried manually typing "-3" in the Q2 cell, without success." what do you mean by without sucess, did it not even refresh the quick pick list?

I'm assuming you're logging your sheet to cell A1 because sometimes people use a different cell.

And you have "Enable triggered betting " ticked , some options don't work without that ticked.

[Captain Sensible]

Are macros enabled on your worksheet and have you simply tried running the loadquickpick list macro from the macro menu?

[nick257]

Thanks again Captain!

If I manually edit Q2, and enter "-3", literally nothing happens (i.e. if quickpick list is showing the previous day's meetings, the QPL is not updated with the current days meetings).

Logging - haven't changed anything here, I use cell A1, on the Market sheet.

The VBA code is fine (I'm fairly confident with VB, I've been using it daily, professionally, for 20+ years

The tip about "Enable Triggered Betting" is interesting... but I'm fairly sure that's not the issue... On some days I run BA with this disabled, and on other days, with it enabled, there doesn't seem to be a correlation here.

I sometimes use BA to monitor the market, i.e. collect data, and other days I use it to bet. It all depends on what my models tell me, i.e if there are stable patterns in the market data. I run 6 (virtual) PCs, each with a separate instance of BA, each using models derived from an A.I. engine, and each model derived from 130+ predictors (independent variables).

Setting up 6 PC's manually each day is a bit pf a pain, hence why I'm trying to solve the quick pick list refresh issue.

[Captain Sensible]

The tip about "Enable Triggered Betting" is interesting... but I'm fairly sure that's not the issue... On some days I run BA with this disabled, and on other days, with it enabled, there doesn't seem to be a correlation here.

Try enabling the Triggered betting and see if it works when you enter -3 in Q2. I'm pretty sure that's likely to be the issue and it's easy enough to check you can just enable trigger betting from the excel enu after you've linked the sheet. Certain excel functions won't work if thats not ticked and I'm sure -3 is one of those.

[Captain Sensible]

The easiest way to check is simply log you sheet to a blank excel sheet without trigger betting enabled.

Go to the menu and clear the quick pick list, then enter -3 into Q2, you'll see with trigger betting not enabled that -3 won't clear from the cell.

Then simply go to the excel menu and select Trigger Betting Enabled, it shoud now pick up the -3 and repopulate the quick pick list to show you it's working and also clear -3 off the excel sheet.

[nick257]

That's fixed it... Thanks Captain.. very much appreciated.