Excel and Guardian SV's

[jimibt]

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]

Now that has me thinking

[jimibt]

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]

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

[PerthO]

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

[firlandsfarm]

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!

[mcgoo]

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]

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

[Dabbla]

How do you add a cell to the value ?

[Dr Ginhog]

Hi,
I love BetAngel I love Excel . BetAngel + excel (Not so much!!! )
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

[ODPaul82]

The name part where you've got +B1 will be throwing it out as that's where it's expecting a stored value name, not the value that's in cell B1
The value will be applied to the current market so you would want something like SET_SV FOR: MARKET NAME:SV_T VALUE:77

This is one which I apply for an individual selection and gets populated into cell L9

SET_SV FOR:SELECTION NAME:LCL VALUE:66666 NAME:UCL VALUE:99999

So I'm setting two SVs at the same time for the selection, one called LCL with a value of 66666 and one for UCL with 99999

[Dr Ginhog]

Hi ODp,
Thanks for the reply.I see what your saying ODp but Im sure the market SV has a different syntax to the selection SV syntax
I did make headway with it last night and got the MARKET assign to initiate (I think) but will have to do further tests
when markets become available. As you can see the assign selection works perfectly but my test did not replicate what i was trying to do
initially and so I cannot yet see the outcome where its actually adding to another Market SV to get a final selection SV value.
The question of whether ASSIGN an SV works to override an existing SV would have also been an issue as that is what I needed it to do for the calculation . My guess is that ASSIGN will not override existing sv of same name. Any ideas on that one? By converting all the Svs to selection will surely work though and I can work to avoid overriding of existing if that is an issue. Markets are coming up at 12am so I shall report back soon.As it appears to work im giving (BetAngel + Excel) a Likey

TEST.png

[Dr Ginhog]

Kudos to Odpaul for pointing out the correct and only way to assign/set market SV

SET_SV FOR:MARKET NAME SV_T VALUE:77
I was completely blindsided on it (and possibly a bit thick also) and i doubted it for a moment. Should never have doubted you ODPaul.
Granted the syntax given is a bit confusing but no excuses. Also dont have to input a MARKET NAME is a bonus. First sight of an OK on the
MARKET SV . ................OK ODPAUL THANK YOU

[Dr Ginhog]

Apart from thanking ODpaul again I would like to offer this tip for using this feature. The feature is only of value if your using a calculated value
from elsewhere in the sheet. You cannot however reference a cell(SAY AR2) in the Syntax at L6 .A workaround is to have your calculated value elsewhere at say AR1 and a copy of the syntax excluding a "value amount " at say AQ1 . You then concatenate AQ1 and AR1 at AR2 using =concat. Thenuse =AR2 only in L6 as your syntax.

[ODPaul82]

Yeah you have to embed into formulas if you want to reference a cell
You don't have to use the concat formula you can just use the & sign

="SET_SV FOR:SELECTION NAME:LCL VALUE:" & ROUND(AG9, 2) & " NAME:UCL VALUE:" & ROUND(AH9,2)