Hi,
I'm just starting using guardian with excel and I was wondering whether someone could help me with this problem: using multiple markets worksheets, and what happens is that, past the first sheet, the others receive the values one column ahead (i.e. values that should populate column A appear in column B and so on). In Guardian settings "origin cell" is set to A1 and, as I said, the first sheet gets populated fine.
Thanks
problem with Guardian/excel
I just wondered whether I'm talking nonsense or nobody knows the answer to my question....
It is a very serious problem that hinders me from using the excel sheets directly from the guardian, hence to use multiple markets concurrently. Any help would be much appreciated.
thanks and happy Xmas
It is a very serious problem that hinders me from using the excel sheets directly from the guardian, hence to use multiple markets concurrently. Any help would be much appreciated.
thanks and happy Xmas
I have a workbook with five different sheets. Every sheet has its own software routines in the calculate section.
I set up guardian distributing the horse races among the sheets and that seems to work well.... there is only one serious problem: guardian doesn't wait the starting time to activate the events, so, what happens is that they cicle in turn each going to its sheet as soon as the workbook is connected.... 1,2,3,4,1,2,3,4 and so on... what's the sense fo all this?
Version of BA I'm using is 1.26.2
any ideas anybody? Bet Angel?
Thanks for any help
I set up guardian distributing the horse races among the sheets and that seems to work well.... there is only one serious problem: guardian doesn't wait the starting time to activate the events, so, what happens is that they cicle in turn each going to its sheet as soon as the workbook is connected.... 1,2,3,4,1,2,3,4 and so on... what's the sense fo all this?
Version of BA I'm using is 1.26.2
any ideas anybody? Bet Angel?
Thanks for any help
Hi Corbus,
I don’t use Guardian but from what I’ve read it cycles through the races at set intervals, updating the data for each race.
You mention that you have code in each worksheet calculate event. Have you anything that says “Activesheet.Select” or “Sheet2.Select” or anything similar?
This will cause the sheets to “cycle” as each one is updated.
I don’t use Guardian but from what I’ve read it cycles through the races at set intervals, updating the data for each race.
You mention that you have code in each worksheet calculate event. Have you anything that says “Activesheet.Select” or “Sheet2.Select” or anything similar?
This will cause the sheets to “cycle” as each one is updated.
Hallo Nigel,
good idea to think about the code... but I checked it, there is no instruction Activate of any kind, then I did even better in order to exclude completely this possibility: instead of connecting my workbook I connected the standard BA workbook BetAngel_Multiple and the same thing happend (i.e. as soon as the WB was connected in the guardian it started sending events to the sheets they were linked to respectively, regardless of their starting times).
Then I did another experiment: I deleted all sheets from the "BetAngel_Multiple", but the first one and made the events all link to that sheet, same thing happend they cicled in turn as soon as connected. On one thing you are right though, the frequence of their cicling is linked to the refresh rate.
If I am the only one to experience this problem, I'd like to read from those users who use Guardian linking to excel without any problems... perhaps we can spot what I'm missing.
Thanks to all
Ennio
good idea to think about the code... but I checked it, there is no instruction Activate of any kind, then I did even better in order to exclude completely this possibility: instead of connecting my workbook I connected the standard BA workbook BetAngel_Multiple and the same thing happend (i.e. as soon as the WB was connected in the guardian it started sending events to the sheets they were linked to respectively, regardless of their starting times).
Then I did another experiment: I deleted all sheets from the "BetAngel_Multiple", but the first one and made the events all link to that sheet, same thing happend they cicled in turn as soon as connected. On one thing you are right though, the frequence of their cicling is linked to the refresh rate.
If I am the only one to experience this problem, I'd like to read from those users who use Guardian linking to excel without any problems... perhaps we can spot what I'm missing.
Thanks to all
Ennio
I can definitely say that Guardian has a few bugs...
consider a list of events linked to excel sheets as follows
event 1 ...................... sheet_1
event 2 ...................... sheet_2
event 3 ...................... sheet_3
event 4 ......................
event 5 ......................
now let's delete the first two lines, so I'd expect the list to look as follows:
event 3 ...................... sheet_3
event 4 ......................
event 5 ......................
but it is not like that at all... it will appear as follows:
event 3 ...................... sheet_1
event 4 ...................... sheet_2
event 5 ...................... sheet_3
which of course can't be correct... it's as if the data in the excel column were not part of the line it's in... and if one sorts the list in some different order, the excel column does not follow the new order at all, it stays unsorted and appears completely wrong...
I'd be grateful to BA if they would answer something to my queries and give some explaination of any kind.
Regards
Ennio
consider a list of events linked to excel sheets as follows
event 1 ...................... sheet_1
event 2 ...................... sheet_2
event 3 ...................... sheet_3
event 4 ......................
event 5 ......................
now let's delete the first two lines, so I'd expect the list to look as follows:
event 3 ...................... sheet_3
event 4 ......................
event 5 ......................
but it is not like that at all... it will appear as follows:
event 3 ...................... sheet_1
event 4 ...................... sheet_2
event 5 ...................... sheet_3
which of course can't be correct... it's as if the data in the excel column were not part of the line it's in... and if one sorts the list in some different order, the excel column does not follow the new order at all, it stays unsorted and appears completely wrong...
I'd be grateful to BA if they would answer something to my queries and give some explaination of any kind.
Regards
Ennio
Hi,
Are you sure you are not getting the sheet names and their positions within the workbook mixed?
Open a blank book and put the following in a module
Sub Test()
Sheets(1).Range("A1") = "Sheet1"
End Sub
If you run it, you will, as expected, get the word "Sheet1" in A1
However, if you now move sheet 3 so that it is the first sheet in the book and run it again, you'll see that sheet 3 ( the FIRST sheet in the book), now has the word "Sheet1" on it.
So Sheet(1), refers to the first sheet in the book, irrespective of the sheet name, and I'm assuming that Guardian uses the sheet postions rather than the sheet name?
Are you sure you are not getting the sheet names and their positions within the workbook mixed?
Open a blank book and put the following in a module
Sub Test()
Sheets(1).Range("A1") = "Sheet1"
End Sub
If you run it, you will, as expected, get the word "Sheet1" in A1
However, if you now move sheet 3 so that it is the first sheet in the book and run it again, you'll see that sheet 3 ( the FIRST sheet in the book), now has the word "Sheet1" on it.
So Sheet(1), refers to the first sheet in the book, irrespective of the sheet name, and I'm assuming that Guardian uses the sheet postions rather than the sheet name?
Hi Nigel,
Fair enough, it could be that Guardian handles sheets that way and one just has to pay attention to that, although, for the sake of argument, I'd say that it's rather important to keep a sheet linked to a particular event because each sheet handles a specific type of event and the events can be different in the Guardian list.... anyway, the big problem though is the first one I'm asking for help with, and if there is no solution to that I can't use Guardian with excel, which is quite a nuisance because it forces me to use several instances of BA and everything is much more complicated to let them talk to each other. So Nigel (are you BA support?), do you think there is a way to let Guardian trigger the events at the correct start times when linked to excel? Does it work for everybody but me?
thanks for your help
Regards
Ennio
Fair enough, it could be that Guardian handles sheets that way and one just has to pay attention to that, although, for the sake of argument, I'd say that it's rather important to keep a sheet linked to a particular event because each sheet handles a specific type of event and the events can be different in the Guardian list.... anyway, the big problem though is the first one I'm asking for help with, and if there is no solution to that I can't use Guardian with excel, which is quite a nuisance because it forces me to use several instances of BA and everything is much more complicated to let them talk to each other. So Nigel (are you BA support?), do you think there is a way to let Guardian trigger the events at the correct start times when linked to excel? Does it work for everybody but me?
thanks for your help
Regards
Ennio
You're right with regards to sheet numbers
I've experienced a little frustration with this, especially when some of my sheets have differing commands.
If I place a match into sheet 4, I don't want it move up to sheets 1-3 when other games are deleted
I get the impression from BA that this isn't easy to overcome
I've experienced a little frustration with this, especially when some of my sheets have differing commands.
If I place a match into sheet 4, I don't want it move up to sheets 1-3 when other games are deleted
I get the impression from BA that this isn't easy to overcome
I quite like the accidental 'feature' that the sheet numbers move up when you delete a market but i guess care is needed to make sure when loading in new markets that they are in the same order as the old ones
We do definitely need an improvement to Guardian excel that doesn't switch between markets before X minutes before the off (like the BA feature) to save wasted refreshes and which would enable you to load more markets in advance, a few of us have asked about this a few times!
We do definitely need an improvement to Guardian excel that doesn't switch between markets before X minutes before the off (like the BA feature) to save wasted refreshes and which would enable you to load more markets in advance, a few of us have asked about this a few times!
Hi top trader,
I wouldn't call it an improvement but rather a fix. As I say on tue 28 above, I work on live horse races and, to avoid overlapping, I have different sheets to which I assign races so that they are well distant one from the other in sequence. So I'd say that's impossible to do as Guardian/Excel works now because the races assigned to the same sheet keep cicling one after the other without awaiting their starting times. So I can only do this by opening a number of instances of BA, with separate Guardian lists and link each Instance to an excel workbook... most of the times three instances of BA with three wotkbooks. Quite messy really and I often run into Betfair's data request overcharge... So I'd say that Guardian/Excel is not doing the job as it is intented to do. I really hope Bet Angel support will look into this and fix it soon.
salut
I wouldn't call it an improvement but rather a fix. As I say on tue 28 above, I work on live horse races and, to avoid overlapping, I have different sheets to which I assign races so that they are well distant one from the other in sequence. So I'd say that's impossible to do as Guardian/Excel works now because the races assigned to the same sheet keep cicling one after the other without awaiting their starting times. So I can only do this by opening a number of instances of BA, with separate Guardian lists and link each Instance to an excel workbook... most of the times three instances of BA with three wotkbooks. Quite messy really and I often run into Betfair's data request overcharge... So I'd say that Guardian/Excel is not doing the job as it is intented to do. I really hope Bet Angel support will look into this and fix it soon.
salut
Hi Nigel,
please tell me whether you are from BA support.
the two buttons don't have any effects. Now the issues are two: the first one is about the links to the separate sheets which in my opinion are not handled properly in Guardian, but this is a minor issue because it's sufficient not to sort or delete any lines and the problem is overcome, although not very elegantly....
the second issue, which concerns the switching times not being applied by Guardian, is much more serious because it defeats the whole idea of authomatic trading with multiple sheets... hence my questions again:
1) does this happen only to me? if so I'd be very happy, because it must be something I'm doing wrong and sooner or later I'll find out what it is.
2) is it a bug in BA? If so I'll abandon the Idea of doing authomatic trading this way until I know that it has been fixed...
Well, good night anyway and a happy 2011.
Ennio
please tell me whether you are from BA support.
the two buttons don't have any effects. Now the issues are two: the first one is about the links to the separate sheets which in my opinion are not handled properly in Guardian, but this is a minor issue because it's sufficient not to sort or delete any lines and the problem is overcome, although not very elegantly....
the second issue, which concerns the switching times not being applied by Guardian, is much more serious because it defeats the whole idea of authomatic trading with multiple sheets... hence my questions again:
1) does this happen only to me? if so I'd be very happy, because it must be something I'm doing wrong and sooner or later I'll find out what it is.
2) is it a bug in BA? If so I'll abandon the Idea of doing authomatic trading this way until I know that it has been fixed...
Well, good night anyway and a happy 2011.
Ennio
Hallo BA,
I've been tackling this problem becaouse I'm very interested in the solution.
The state of the art is this:
1) Guardian ----> BA -----> Excel = OK
2) Guardian --------------> Excel single mkt = OK
3) Guardian --------------> Excel mltpl mkt = NOK
Guardian ---> excel will work as long as we don't fill in the "Excel sheet" column, if we do, even if we have a single excel sheet onto which we force some of the events through the link(wouldn't have any sense, but only for the sake of this exercise), Guardian will send those events at every refresh, and so it will cease to do its job!!
So, sinthesising:
IF ONE FILLS IN THE "excel sheet" COLUMN OF AN EVENT, THE GUARDIAN WILL PICK UP THE TIME TO LOAD THAT EVENT FROM THE "last update" COLUMN INSTEAD OF THE "start time" COLUMN.
Of course all events with an entry in the "excel sheet" column get loaded into excel as soon as the refresh is done and EVERY TIME the refresh is done to them.
So, I'd say the solution is quite simple and very urgent because so far, I believe, nobody has been able to use the multiple sheet function at all.
As I want to be sure that BA support get this message I'll send it also directly to them, I hope they won't find it a nuisance..
Greetings..
Ennio
I've been tackling this problem becaouse I'm very interested in the solution.
The state of the art is this:
1) Guardian ----> BA -----> Excel = OK
2) Guardian --------------> Excel single mkt = OK
3) Guardian --------------> Excel mltpl mkt = NOK
Guardian ---> excel will work as long as we don't fill in the "Excel sheet" column, if we do, even if we have a single excel sheet onto which we force some of the events through the link(wouldn't have any sense, but only for the sake of this exercise), Guardian will send those events at every refresh, and so it will cease to do its job!!
So, sinthesising:
IF ONE FILLS IN THE "excel sheet" COLUMN OF AN EVENT, THE GUARDIAN WILL PICK UP THE TIME TO LOAD THAT EVENT FROM THE "last update" COLUMN INSTEAD OF THE "start time" COLUMN.
Of course all events with an entry in the "excel sheet" column get loaded into excel as soon as the refresh is done and EVERY TIME the refresh is done to them.
So, I'd say the solution is quite simple and very urgent because so far, I believe, nobody has been able to use the multiple sheet function at all.
As I want to be sure that BA support get this message I'll send it also directly to them, I hope they won't find it a nuisance..
Greetings..
Ennio
Thanks for getting in touch with us via support. To clarify for everybody: -
There are two primary ways to use Guardian and Excel: -
(1) Link via the 'Excel' tab in Bet Angel
When you do this, if you populate Guardian with markets it will switch to that market X seconds before the off. It will cycle through other markets in the background to check on their start time and switch when required.
(2) Link via the 'sheets' column in Guardian
When you link actual sheets to in Guardian it will cycle those sheets at the rate you have nominated to update the odds. If you are active on a market it will refresh that at the prevailing rate in Bet Angel. Any activities on that or any other sheets will trigger a position via Bet Angel even if Bet Angel is currently not viewing that market or in the process of cycling it. So, for example, you can have a spreadsheet timed to put a bet in the market at 17:00 and Bet Angel will action that if it detects an that action from one of those spreadsheets.
You should also note that if Bet Angel detects a market is settled in Guardian it will not attempt to refresh that market, it will just skip it.
We realise that people don’t like the re-ordering of spreadsheets when a link is removed. So we will have a look at solving that issue in the next build.
There are two primary ways to use Guardian and Excel: -
(1) Link via the 'Excel' tab in Bet Angel
When you do this, if you populate Guardian with markets it will switch to that market X seconds before the off. It will cycle through other markets in the background to check on their start time and switch when required.
(2) Link via the 'sheets' column in Guardian
When you link actual sheets to in Guardian it will cycle those sheets at the rate you have nominated to update the odds. If you are active on a market it will refresh that at the prevailing rate in Bet Angel. Any activities on that or any other sheets will trigger a position via Bet Angel even if Bet Angel is currently not viewing that market or in the process of cycling it. So, for example, you can have a spreadsheet timed to put a bet in the market at 17:00 and Bet Angel will action that if it detects an that action from one of those spreadsheets.
You should also note that if Bet Angel detects a market is settled in Guardian it will not attempt to refresh that market, it will just skip it.
We realise that people don’t like the re-ordering of spreadsheets when a link is removed. So we will have a look at solving that issue in the next build.