A couple of quick questions if I may ...
I use Excel 2016, is it OK to use xlsx files to connect to BA or should I stick to xls files? I ask because when I open the xls files supplied they open in Compatibility Mode and when I look to save I get of error messages about things that cannot be saved because of compatibility problems whereas if I save in the newer xlsx format I get no such error messages.
Also I like to be able to see what I want to see on one screen as much as possible, without scrolling. As I'm not trading there are some screens I'm not bothered about seeing such as prices and market size as I will be coding limits for them in my cells where I set the bet criteria so is it OK to change the column order by cutting/inserting or simply moving them as I would in an ordinary workbook or is the position of some locations hard coded into whatever code you are using and not moveable?
Farmer
First try Do's and Dont's with First Excel Workbook Project!
-
firlandsfarm
- Posts: 2722
- Joined: Sat May 03, 2014 8:20 am
Last edited by firlandsfarm on Tue Aug 08, 2017 9:34 am, edited 2 times in total.
Saving as xlsx is fine.
To maximise the screen display, right click anywhere on the bottom of the ribbon and select "minimise the ribbon". You can also get rid of the formula bar and headings by unticking them from the view tab.
When you say
To maximise the screen display, right click anywhere on the bottom of the ribbon and select "minimise the ribbon". You can also get rid of the formula bar and headings by unticking them from the view tab.
When you say
do you mean the Bet Angel columns or any vba code that you write?is the position of some locations hard coded into whatever code you are using and not moveable
-
firlandsfarm
- Posts: 2722
- Joined: Sat May 03, 2014 8:20 am
Hi Nigel, thanks for helping.
I'll use xlsx, much easier for me.
When referring to viewing all on the same screen I'm referring to width/columns not depth/rows so I'm afraid ribbons and formula bars don't come into it. I will be adding columns to the right of the BA columns so I can see what my custom columns are showing when bets are placed to get that 'feel safe monitoring' feeling until I am happy with the way my criteria is being interpreted by Excel. (rather than have long single complicated expressions in one cell I prefer to break them down into one stage/criteria per cell to make editing/de-bugging easier). For now I have just narrowed the columns I'm not interested in but to my mind that's a work-around and not good practice! (I'm not much of a VBA man either, I have used VBA a few times but it requires me to learn from new what I need to do and tbh I only do it when I can't see a way using just cells!). Ideally as a first move I would want to move the 2nd and 3rd Back and Lay prices to the right so that I just have the front-line prices in view ... I can of course do this (I assume) by having my own columns on the right (say starting at column BA) and actively copy/format the columns I want from the BA stuff to 'my' columns to the right of column BA by selecting and filtering, maybe that's the easiest way to make a custom display on the worksheet.
I'll use xlsx, much easier for me.
When referring to viewing all on the same screen I'm referring to width/columns not depth/rows so I'm afraid ribbons and formula bars don't come into it. I will be adding columns to the right of the BA columns so I can see what my custom columns are showing when bets are placed to get that 'feel safe monitoring' feeling until I am happy with the way my criteria is being interpreted by Excel. (rather than have long single complicated expressions in one cell I prefer to break them down into one stage/criteria per cell to make editing/de-bugging easier). For now I have just narrowed the columns I'm not interested in but to my mind that's a work-around and not good practice! (I'm not much of a VBA man either, I have used VBA a few times but it requires me to learn from new what I need to do and tbh I only do it when I can't see a way using just cells!). Ideally as a first move I would want to move the 2nd and 3rd Back and Lay prices to the right so that I just have the front-line prices in view ... I can of course do this (I assume) by having my own columns on the right (say starting at column BA) and actively copy/format the columns I want from the BA stuff to 'my' columns to the right of column BA by selecting and filtering, maybe that's the easiest way to make a custom display on the worksheet.
.xlsx and .xlsm files work fine, they just dont show up in the "browse for file" window unless you change the "show files of type" selection to "all files *.*"
The newer format files as above is superior to the old format .xls files because a) a sheet can contain up to 1 million rows and b) the saved file is compressed and uses less disk space
The newer format files as above is superior to the old format .xls files because a) a sheet can contain up to 1 million rows and b) the saved file is compressed and uses less disk space
-
firlandsfarm
- Posts: 2722
- Joined: Sat May 03, 2014 8:20 am
Hi all, the project is coming together nicely and I did my first test of non-interference betting yesterday and all bets were to spec 
but I have noticed a few things that I would like a little help with if you can.
Guardian Excel v's 'BA' Excel
Peter covers that you can connect to an Excel workbook via these two routes in his video "Connecting Bet Angel to a spreadsheet" and I thought he was saying "it doesn't matter which way you do it they both connect the same" but they don't, they are two separate connections. Clearly the Guardian connection is for Guardian but what is the 'BA' connection for? How does it enhance the main BA functions?
Multiple Markets Excel v's Single Market Excel
In the same video at the 3:00 minute mark Peter explains that by using the "Automatically switch Bet Angel market" in Guardian you can get BA to change to the next market at the predetermined interval before the Off. As I intend to scroll through the day's racing analysing and placing bets one or two minutes before the Off I thought using the single sheet workbook and cycle through the day by switching to next market would be the way to go and expected the spreadsheet to load the first market, hold it and then move on to the next market at the prescribed time but no. Excel just kept scrolling through all Guardian selected markets one per refresh interval. I'm looking for a 'set & forget' system to allow me to do things during the day/evening but if there are 43 races in the UK and Ireland today and with a refresh rate of 1 second it will take 43 seconds to cycle through the day's racing. (Yes, I can speed up the refresh rate but I'm concerned that if the market is active for less time then maybe Excel/BA won't get the time to react to a possible bet for placing and it gets missed also not so much chance to represent the bet if it gets 'lost'.
) Is there another setting somewhere that limits the worksheet to the current Guardian market only as it does with the main BA display? The single sheet route is preferable because otherwise I will need 43 worksheets today for example and if I overlaid a custom display for each that's 86 worksheets (I must get this resolved by next Saturday!! 
) and each market is only active for the last minute before the Off so only one market active at a time.
Clearing Worksheet pages
I have noticed that a worksheet sometimes retains some (BA) values after a new market has been loaded (Bet Status in particular). Is there a way to clear all old values when loading a new market? As a workaround I'm using the Unmatched/Matched columns (columns T - AE) as my check values because they are obviously live not just to the market but also the runner order should it change.
but I have noticed a few things that I would like a little help with if you can.Guardian Excel v's 'BA' Excel
Peter covers that you can connect to an Excel workbook via these two routes in his video "Connecting Bet Angel to a spreadsheet" and I thought he was saying "it doesn't matter which way you do it they both connect the same" but they don't, they are two separate connections. Clearly the Guardian connection is for Guardian but what is the 'BA' connection for? How does it enhance the main BA functions?
Multiple Markets Excel v's Single Market Excel
In the same video at the 3:00 minute mark Peter explains that by using the "Automatically switch Bet Angel market" in Guardian you can get BA to change to the next market at the predetermined interval before the Off. As I intend to scroll through the day's racing analysing and placing bets one or two minutes before the Off I thought using the single sheet workbook and cycle through the day by switching to next market would be the way to go and expected the spreadsheet to load the first market, hold it and then move on to the next market at the prescribed time but no. Excel just kept scrolling through all Guardian selected markets one per refresh interval. I'm looking for a 'set & forget' system to allow me to do things during the day/evening but if there are 43 races in the UK and Ireland today and with a refresh rate of 1 second it will take 43 seconds to cycle through the day's racing. (Yes, I can speed up the refresh rate but I'm concerned that if the market is active for less time then maybe Excel/BA won't get the time to react to a possible bet for placing and it gets missed also not so much chance to represent the bet if it gets 'lost'.
) Is there another setting somewhere that limits the worksheet to the current Guardian market only as it does with the main BA display? The single sheet route is preferable because otherwise I will need 43 worksheets today for example and if I overlaid a custom display for each that's 86 worksheets (I must get this resolved by next Saturday!! ) and each market is only active for the last minute before the Off so only one market active at a time. Clearing Worksheet pages
I have noticed that a worksheet sometimes retains some (BA) values after a new market has been loaded (Bet Status in particular). Is there a way to clear all old values when loading a new market? As a workaround I'm using the Unmatched/Matched columns (columns T - AE) as my check values because they are obviously live not just to the market but also the runner order should it change.
Last edited by firlandsfarm on Tue Aug 08, 2017 9:33 am, edited 1 time in total.
-
firlandsfarm
- Posts: 2722
- Joined: Sat May 03, 2014 8:20 am
Sorry, me again! I cannot seem the get the Global Command "TAKE_SP_ALL" to have any effect (assuming it means take SP on any unmatched bets at the Off). I "IF'd" it to happen 30 secs before race start time (bets are now placed 2 mins before the Off) but that had no effect on unmatched bets so I hard typed it directly into the Global Command cell thinking maybe it has to be active when the bet is placed but still no effect. How is this command used? I am entering it into the 'green' highlighted cell under the cell labelled "Global Command" ... the Global Status shows as "OK" but it's not, is it! 
Hey mate,
Apologies I only read one of your questions regarding clearing sheet (it's late here).
Using VBA conditional statement you can check if a cells value has changed by
Storing a cells value In a global variable then rechecking it every interval. Once change is detected you can trigger your code to clear cells. This will work best with the cell which stores the market name.
Apologies I only read one of your questions regarding clearing sheet (it's late here).
Using VBA conditional statement you can check if a cells value has changed by
Storing a cells value In a global variable then rechecking it every interval. Once change is detected you can trigger your code to clear cells. This will work best with the cell which stores the market name.
-
firlandsfarm
- Posts: 2722
- Joined: Sat May 03, 2014 8:20 am
Thanks Workpeter ... I'm not really into VBA. I know I should be but it a big learn and while I can use workarounds as I am there is always something more important to do! I am surprised that BA doesn't clear all the BA controlled cells when a market changes, I mean what relevance can it be to a market that a bet was "PLACED" on a selection in the previous market that occupied the same row in the Price Table?