Excel and Guardian SV's

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
User avatar
firlandsfarm
Posts: 2686
Joined: Sat May 03, 2014 8:20 am

I wish you all the best both educationally and for health. I'm 69 so just miss out on 4 months not having to socialise!! :lol: :lol: :lol:
User avatar
jimibt
Posts: 3641
Joined: Mon Nov 30, 2015 6:42 pm
Location: Narnia

sa7med wrote:
Mon Mar 16, 2020 9:16 am
sniffer66 wrote:
Mon Mar 16, 2020 8:48 am
Hope you dont mind me piggybacking on this but it's kind of relevant.

I've been focusing on Guradian automation for a while but was intrigued by the new ability to link to Excel for SV's. One of the abilities I was hoping this might bring to the table would be to use Excel as a kind of lookup table for stats. Thinking specifically about tennis where I might have a season\career stat for service holds\break back etc
Only thing I can't get my head around is that this would need to be selection specific and I dont believe we can address selection names from Guardian.

So set an SV in Excel with the value of all (or current match) players Break Back %. Then in Guardian: IF selection is Player name x AND Break Back % SV > y THEN Back the selection

I'm guessing this isn't possible currently.
Not sure what the issue is, but if you're looking up some stats for a particular play and you want to back him/her based on certain criteria then just set a stored value for that player (selection) no when criteria is met then make that stored value a condition for placing a rule in guardian automation? You don't need the player name to pass to guardian, just that the selection has a certain stored value.

For example.

You have a table with names and stats. You lookup B9 or B11 using index/match or whatever to find the value you're looking for in that table. Then in your rules section: If value meets criteria then set a stored value for that selection. In guardian, base your rules on that stored value.

Hope I didn't misunderstand your question. Best of luck.
100% concur. Based on the fact that Excel can handle SV's now, I personally would do all my crucnhing inside Excel and simply return the updated SV values to Guardian to perform the bet placement.

Previously, the only communication was via Global Commands (placing bets, Cancelling bets etc). Using Excel as the strategy engine and continuing to use a fairly bland rule in BA to react to the changing SV's is the way fwd. Great to have the mix of both but I will no longer use the Excel Global Commands to deal with bet placement - horses for courses (and logical progression). In effect, one can use a mix 'n match fo generating SV's inside either BA and/or Excel and processing as required.

Will TRY and setup a small example of an excel template with a companion BA rule that demonstrates how we remove a high degree of friction by adopting this workflow.

[edit] just found a very useful function of the command line to pass SV's. You can pass multiple values for a selection, i.e chained SV's. e.g:

SET_SV FOR:SELECTION NAME:Test1 VALUE:42 NAME:Test2 Value:99 NAME:Test3 Value:200

This means that you can send multiple SV values back to Guardian in a single action -very useful for when Guardian has to act when 1 or more SV's have a simultaneous new value. see: https://www.betangel.com/user-guide/v1_ ... 3D&mw=MzIw
sniffer66
Posts: 1666
Joined: Thu May 02, 2019 8:37 am

jimibt wrote:
Mon Mar 16, 2020 10:43 am
sa7med wrote:
Mon Mar 16, 2020 9:16 am
sniffer66 wrote:
Mon Mar 16, 2020 8:48 am
Hope you dont mind me piggybacking on this but it's kind of relevant.

I've been focusing on Guradian automation for a while but was intrigued by the new ability to link to Excel for SV's. One of the abilities I was hoping this might bring to the table would be to use Excel as a kind of lookup table for stats. Thinking specifically about tennis where I might have a season\career stat for service holds\break back etc
Only thing I can't get my head around is that this would need to be selection specific and I dont believe we can address selection names from Guardian.

So set an SV in Excel with the value of all (or current match) players Break Back %. Then in Guardian: IF selection is Player name x AND Break Back % SV > y THEN Back the selection

I'm guessing this isn't possible currently.
Not sure what the issue is, but if you're looking up some stats for a particular play and you want to back him/her based on certain criteria then just set a stored value for that player (selection) no when criteria is met then make that stored value a condition for placing a rule in guardian automation? You don't need the player name to pass to guardian, just that the selection has a certain stored value.

For example.

You have a table with names and stats. You lookup B9 or B11 using index/match or whatever to find the value you're looking for in that table. Then in your rules section: If value meets criteria then set a stored value for that selection. In guardian, base your rules on that stored value.

Hope I didn't misunderstand your question. Best of luck.
100% concur. Based on the fact that Excel can handle SV's now, I personally would do all my crucnhing inside Excel and simply return the updated SV values to Guardian to perform the bet placement.

Previously, the only communication was via Global Commands (placing bets, Cancelling bets etc). Using Excel as the strategy engine and continuing to use a fairly bland rule in BA to react to the changing SV's is the way fwd. Great to have the mix of both but I will no longer use the Excel Global Commands to deal with bet placement - horses for courses (and logical progression). In effect, one can use a mix 'n match fo generating SV's inside either BA and/or Excel and processing as required.

Will TRY and setup a small example of an excel template with a companion BA rule that demonstrates how we remove a high degree of friction by adopting this workflow.

[edit] just found a very useful function of the command line to pass SV's. You can pass multiple values for a selection, i.e chained SV's. e.g:

SET_SV FOR:SELECTION NAME:Test1 VALUE:42 NAME:Test2 Value:99 NAME:Test3 Value:200

This means that you can send multiple SV values back to Guardian in a single action -very useful for when Guardian has to act when 1 or more SV's have a simultaneous new value. see: https://www.betangel.com/user-guide/v1_ ... 3D&mw=MzIw
Thanks Jim. An example flow would be appreciated and would be great way for me to get my head around the setup.

One thing did strike me thinking through this and that is that it would be great if we had a command line interface directly in to BA Guardian.
Then it would be possible to use any scripting/coding tool to pass your example commmand below

SET_SV FOR:SELECTION NAME:Test1 VALUE:42 NAME:Test2 Value:99 NAME:Test3 Value:200

Then we are not bound solely by Excel but can manipulate the data in any way we like, using Excel from the script if needed.
For instance I use Auto-it a lot and being able to scrape, build arrays from CSV's\ XLXS etc and then do x, y and z with it would be very powerful.

Then again, I'm probably thinking along those lines as I'm crap with Excel lol. Time to get to work on that !
User avatar
jimibt
Posts: 3641
Joined: Mon Nov 30, 2015 6:42 pm
Location: Narnia

sniffer66 wrote:
Mon Mar 16, 2020 11:43 am
Thanks Jim. An example flow would be appreciated and would be great way for me to get my head around the setup.

One thing did strike me thinking through this and that is that it would be great if we had a command line interface directly in to BA Guardian.
Then it would be possible to use any scripting/coding tool to pass your example commmand below

SET_SV FOR:SELECTION NAME:Test1 VALUE:42 NAME:Test2 Value:99 NAME:Test3 Value:200

Then we are not bound solely by Excel but can manipulate the data in any way we like, using Excel from the script if needed.
For instance I use Auto-it a lot and being able to scrape, build arrays from CSV's\ XLXS etc and then do x, y and z with it would be very powerful.

Then again, I'm probbaly thinking along those lines as I'm crap with Excel lol. Time to get to work on that !
So, what I have actually done in reality is to run .net code inside Excel and then pass and manipluate the output in Excel to place the bets. By using SV's, I will pretty much simplify my setup by still using .net for the strategy engine. However, I will change the logic of the output in Excel to literally populate the approapiate cell with SET_SV FOR:SELECTION NAME:Test1 VALUE:42 NAME:Test2 Value:99 NAME:Test3 Value:200. I'll then leave the BA rule to do the rest and notify back to Excel if required (i.e. send back a notifiction SV to Excel which would in turn inform the .net code of the next steps -if any).

I actually made a request a few years back to have an interface (i.e. a software contract) exposed by BA that would allow us (as developers) to plugin to the BA code via our dll's. For one reason or another, this was not viable and this is when I adopted the approach of using Excel to place the bets (via the global command) in response to events raised by the .net code. Things just got a degree more frictionfree as a result of the SV integration. I will now use Excel as an adaptor onto my .net code and the SV's as a way to promote the action back up to BA.

Hope this makes sense ;)
sniffer66
Posts: 1666
Joined: Thu May 02, 2019 8:37 am

jimibt wrote:
Mon Mar 16, 2020 11:57 am
sniffer66 wrote:
Mon Mar 16, 2020 11:43 am
Thanks Jim. An example flow would be appreciated and would be great way for me to get my head around the setup.

One thing did strike me thinking through this and that is that it would be great if we had a command line interface directly in to BA Guardian.
Then it would be possible to use any scripting/coding tool to pass your example commmand below

SET_SV FOR:SELECTION NAME:Test1 VALUE:42 NAME:Test2 Value:99 NAME:Test3 Value:200

Then we are not bound solely by Excel but can manipulate the data in any way we like, using Excel from the script if needed.
For instance I use Auto-it a lot and being able to scrape, build arrays from CSV's\ XLXS etc and then do x, y and z with it would be very powerful.

Then again, I'm probbaly thinking along those lines as I'm crap with Excel lol. Time to get to work on that !
So, what I have actually done in reality is to run .net code inside Excel and then pass and manipluate the output in Excel to place the bets. By using SV's, I will pretty much simplify my setup by still using .net for the strategy engine. However, I will change the logic of the output in Excel to literally populate the approapiate cell with SET_SV FOR:SELECTION NAME:Test1 VALUE:42 NAME:Test2 Value:99 NAME:Test3 Value:200. I'll then leave the BA rule to do the rest and notify back to Excel if required (i.e. send back a notifiction SV to Excel which would in turn inform the .net code of the next steps -if any).

I actually made a request a few years back to have an interface (i.e. a software contract) exposed by BA that would allow us (as developers) to plugin to the BA code via our dll's. For one reason or another, this was not viable and this is when I adopted the approach of using Excel to place the bets (via the global command) in response to events raised by the .net code. Things just got a degree more frictionfree as a result of the SV integration. I will now use Excel as an adaptor onto my .net code and the SV's as a way to promote the action back up to BA.

Hope this makes sense ;)
I think that makes sense. You are purely using Excel as a gateway\conduit between your .net code and BA ?

On that basis anything that could write\read from an Excel cell would also be viable to use ?
User avatar
jimibt
Posts: 3641
Joined: Mon Nov 30, 2015 6:42 pm
Location: Narnia

sniffer66 wrote:
Mon Mar 16, 2020 12:47 pm
jimibt wrote:
Mon Mar 16, 2020 11:57 am
sniffer66 wrote:
Mon Mar 16, 2020 11:43 am
Thanks Jim. An example flow would be appreciated and would be great way for me to get my head around the setup.

One thing did strike me thinking through this and that is that it would be great if we had a command line interface directly in to BA Guardian.
Then it would be possible to use any scripting/coding tool to pass your example commmand below

SET_SV FOR:SELECTION NAME:Test1 VALUE:42 NAME:Test2 Value:99 NAME:Test3 Value:200

Then we are not bound solely by Excel but can manipulate the data in any way we like, using Excel from the script if needed.
For instance I use Auto-it a lot and being able to scrape, build arrays from CSV's\ XLXS etc and then do x, y and z with it would be very powerful.

Then again, I'm probbaly thinking along those lines as I'm crap with Excel lol. Time to get to work on that !
So, what I have actually done in reality is to run .net code inside Excel and then pass and manipluate the output in Excel to place the bets. By using SV's, I will pretty much simplify my setup by still using .net for the strategy engine. However, I will change the logic of the output in Excel to literally populate the approapiate cell with SET_SV FOR:SELECTION NAME:Test1 VALUE:42 NAME:Test2 Value:99 NAME:Test3 Value:200. I'll then leave the BA rule to do the rest and notify back to Excel if required (i.e. send back a notifiction SV to Excel which would in turn inform the .net code of the next steps -if any).

I actually made a request a few years back to have an interface (i.e. a software contract) exposed by BA that would allow us (as developers) to plugin to the BA code via our dll's. For one reason or another, this was not viable and this is when I adopted the approach of using Excel to place the bets (via the global command) in response to events raised by the .net code. Things just got a degree more frictionfree as a result of the SV integration. I will now use Excel as an adaptor onto my .net code and the SV's as a way to promote the action back up to BA.

Hope this makes sense ;)
I think that makes sense. You are purely using Excel as a gateway\conduit between your .net code and BA ?

On that basis anything that could write\read from an Excel cell would also be viable to use ?
in a nutshell -yes ;)
sniffer66
Posts: 1666
Joined: Thu May 02, 2019 8:37 am

Now that has me thinking :)
User avatar
jimibt
Posts: 3641
Joined: Mon Nov 30, 2015 6:42 pm
Location: Narnia

sniffer66 wrote:
Mon Mar 16, 2020 1:43 pm
Now that has me thinking :)
of course, the BA Sheet in the excel template MUST correspond in structure to the expected template that uses the office interop to and from BA ;)

i.e. as long as you retain the default BA Sheet to transfer data to and from BA, the rest can be whatever works for you.
Atho55
Posts: 637
Joined: Tue Oct 06, 2015 1:37 pm
Location: Home of Triumph Motorcycles

Has anyone managed to get a cell reference inserted as the Value get a SV value back in Guardian. Would be interested to know how.

Would I be right in thinking that the fiasco of clearing Status Cells has to be overcome to send this data a second time?

Status Cell.jpg
You do not have the required permissions to view the files attached to this post.
PerthO
Posts: 7
Joined: Thu Feb 13, 2020 11:56 pm

Hi Atho55

You may want something like this (substitute in appropriate cell references) : ="SET_SV FOR:SELECTION NAME:SPREAD_TICKS VALUE:" & H35 & " NAME:SPREAD_BETS VALUE:" & $B10 & " NAME:UNMATCHED_BACKS VALUE:" & H39 .........etc."

To overcome the refresh problem, I have the following VBA :

Private Sub Worksheet_Calculate()
If (Range("O9").Value = "PLACED") Or (Range("O9").Value = "ERROR") Or (Range("O9").Value = "OK") Or (Range("O9").Value = "Placing") Then Range("O9").ClearContents
If (Range("O11").Value = "PLACED") Or (Range("O11").Value = "ERROR") Or (Range("O11").Value = "OK") Or (Range("O11").Value = "Placing") Then Range("O11").ClearContents
If (Range("O13").Value = "PLACED") Or (Range("O13").Value = "ERROR") Or (Range("O13").Value = "OK") Or (Range("O13").Value = "Placing") Then Range("O13").ClearContents
If (Range("O15").Value = "PLACED") Or (Range("O15").Value = "ERROR") Or (Range("O15").Value = "OK") Or (Range("O15").Value = "Placing") Then Range("O15").ClearContents
If (Range("O17").Value = "PLACED") Or (Range("O17").Value = "ERROR") Or (Range("O17").Value = "OK") Or (Range("O17").Value = "Placing") Then Range("O17").ClearContents
End Sub

You can then have constantly updating SVs above each column by editing the settings.

Hope this helps.

PerthO
User avatar
firlandsfarm
Posts: 2686
Joined: Sat May 03, 2014 8:20 am

PerthO wrote:
Mon Aug 31, 2020 12:00 pm
Hi Atho55

You may want something like this (substitute in appropriate cell references) : ="SET_SV FOR:SELECTION NAME:SPREAD_TICKS VALUE:" & H35 & " NAME:SPREAD_BETS VALUE:" & $B10 & " NAME:UNMATCHED_BACKS VALUE:" & H39 .........etc."

To overcome the refresh problem, I have the following VBA :

Private Sub Worksheet_Calculate()
If (Range("O9").Value = "PLACED") Or (Range("O9").Value = "ERROR") Or (Range("O9").Value = "OK") Or (Range("O9").Value = "Placing") Then Range("O9").ClearContents
If (Range("O11").Value = "PLACED") Or (Range("O11").Value = "ERROR") Or (Range("O11").Value = "OK") Or (Range("O11").Value = "Placing") Then Range("O11").ClearContents
If (Range("O13").Value = "PLACED") Or (Range("O13").Value = "ERROR") Or (Range("O13").Value = "OK") Or (Range("O13").Value = "Placing") Then Range("O13").ClearContents
If (Range("O15").Value = "PLACED") Or (Range("O15").Value = "ERROR") Or (Range("O15").Value = "OK") Or (Range("O15").Value = "Placing") Then Range("O15").ClearContents
If (Range("O17").Value = "PLACED") Or (Range("O17").Value = "ERROR") Or (Range("O17").Value = "OK") Or (Range("O17").Value = "Placing") Then Range("O17").ClearContents
End Sub

You can then have constantly updating SVs above each column by editing the settings.

Hope this helps.

PerthO
Hi PerthO, thanks for sharing that code, I've had many battles with clearing the Status cells … some I've won and some it's won! But your approach is different to others I've seen, will give it some thought when I next go into battle. My initial thought is that it probably clears the Status cells instantly so maybe some form of time delay might come in handy to give yourself a chance to see what's happening and/or stop in an emergency! :)
User avatar
mcgoo
Posts: 898
Joined: Thu Jul 18, 2013 12:30 pm

Atho55 wrote:
Tue Aug 25, 2020 7:26 pm
Has anyone managed to get a cell reference inserted as the Value get a SV value back in Guardian. Would be interested to know how.

Would I be right in thinking that the fiasco of clearing Status Cells has to be overcome to send this data a second time?


Status Cell.jpg
This would be good. I want to send the moving average, for example, back to Guardian which I can calculate in a cell (and it varies so much)- not sure if I have this correct but it seems bloody hard to nut out how to track the MA dynamically from Excel? ..unless there is a way now?..of course popping moving averages and things like WOM into history lists would be good too :)
sniffer66
Posts: 1666
Joined: Thu May 02, 2019 8:37 am

Might be a little irrelevant to those of you are are relying on Excel but I found out something interesting this morning...

I'm using a script that web scrapes and adds pre off Text Comparison values directly to a baf, by loading the baf into an array and addressing by string search & row numbers. Saving the edited baf then immediately updates the values in the loaded & applied baf. That works a treat.

After reading this I tried a quick test on an SV. I set up a test SV that wrote to the log every 10 seconds, applied it to a market, then edited the SV value in the baf, using Notepad++, then saved it

Capture.JPG

On notepad save, it didn't re-apply the entire baf (which would reset many of my SV's) but it did update the value, once I clicked "Edit Rules file"
I could do that "Edit Rules file" click/close very easily in the same Auto-It script.

Its a little clunky but it does open up some interesting possibilities to get data into an applied baf from various sources
You do not have the required permissions to view the files attached to this post.
User avatar
Dabbla
Posts: 662
Joined: Wed Apr 15, 2009 1:50 pm

How do you add a cell to the value ? :?
Dr Ginhog
Posts: 260
Joined: Wed Nov 03, 2010 12:10 am
Location: Ballygarvan,Cork Ireland T12D2VR
Contact:

Hi,
I love BetAngel I love Excel . BetAngel + excel (Not so much!!! :D )
Can somebody help here with assigning an SV to (an automation file if thats what it should be doing)
Ive tried a trillion variations- cleared the error .Closed/opened excel. etc Can you use +B1 or B1 or =B1 or =(B1) instead of the market name
Can the market name be ignored altogether as its default for the market your in??? While I have the alternative set sv using a file this is
annoyingly frustrating and im curious to know.
WTFFFFFFFFF.png
You do not have the required permissions to view the files attached to this post.
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”