Dear all,
Has anyone managed to get the results of one Excel Sheet autotrading in prac mode on one strategy to then trigger the bet on a separate sheet running in Live Mode on a separate BA account following a specifc event.
I have the necessary VBA macros working when both sheets are open normally but once opened via Betangel I get an error because the Live sheet is not recognised.
The Code below worked just copying the contents of one cell in the prac mode sheet to the necessary cell of the live mode sheet to trigger the live bet
Workbooks("20130601 Horses Auto Win One Lay Live v1 gvb.xlsm").Worksheets("Bet Angel").Range("D4") = Worksheets("Sheet1").Range("C7")
I also tried doing it by recording a macro when connected to BA but the bits related to the 2nd sheet will not record!
I have also tried the following code having recorded the macro
Sheets("Sheet1").Select
Range("C7").Select
Selection.Copy
Windows("20130601 Horses Auto Win One Lay Live v1 gvb.xlsm").Activate
Range("D4").Select
ActiveSheet.Paste
Windows("20130601 Horses Auto Win One Lay Prac v1 gvb.xls.xlsm").Activate
Sheets("Bet Angel").Select
Range("D5").Select
Any ideas anyone, grateful for some assistance.
yours Gareth
Linking 2 Excel Sheets running on separate BA accounts?
You cannot dynamically link two separate instances of Excel and have live streaming data moving between the two. Any linked sheets have to be in the same instance. Each instance of Bet Angel opens a new instance of Excel.
You can link to a saved version of a sheet on your hard drive so setting up some kind of auto-save every few seconds might be a solution. I expect this would get very tedious with the computer pausing to save a file and then read it in again each time.
You can link to a saved version of a sheet on your hard drive so setting up some kind of auto-save every few seconds might be a solution. I expect this would get very tedious with the computer pausing to save a file and then read it in again each time.
Thanks 6th Sense, I thought that might be the case but wanted confirmation from someone better than me!! I might use the saved method to pass the necessary data and use end of race and start of race as the trigger to check the file; might work. Thanks again, saved me a load of trial and error. Gareth
6th Sense is correct - you can't move data between different instances of Excel in that way. You could probably have used DDE in older versions of Excel, but that was fraught with problems.
Three other ways spring to mind. One *interesting, one boring but quite easy and the last the best but a bit harder. It really depends on how much effort you want to put into it.
Option 1 - Use a 'shared' intermediary workbook
You have an intermediary workbook that has data pasted into it from the Practice BA workbook and the Live BA workbook then reads that data.
Basically, once you've opened the main workbook from one instance of BA, you then open the extra workbook (call it middle.xlsx). Both workbooks are running in the same instance of Excel so you can push data into the 'middle.xlsx' book.
Then, from your second instance of BA, open it's own workbook. From that copy of Excel, open the same 'middle.xlsx' file.
You can push (paste) data into middle.xlsx and it will appear in the second copy of middle.xlsx so your Macros in your second BA workbook can read (copy) the data.
Bit convoluted, but workable. Middle.xlsx needs to be set as 'shared', and you'd need to add some VBA code with a timer into it so that it saved every X seconds (data is only added to the workbook from the 1st instance, when both workbooks are saved).
You need this intermediary workbook because if you opened the same copy of the BA workbook in shared mode, then the data put into it from BA would travel in both directions - getting very confusing.
Option 2 - Write the data to a file from the practice BA workbook and then open the file and read the data from your live BA workbook. You need to use error checking so that you handle contention (file can't be opened for reading, if it's also open for writing).
Option 3 - Use a Database. Same as option 2, but let the DB handle the locking.
An interesting problem to solve with a shared workbook, but in practice, you might find it easier to just write the data to a file.
Regards
Ian
* Well, I find it interesting anyway
Three other ways spring to mind. One *interesting, one boring but quite easy and the last the best but a bit harder. It really depends on how much effort you want to put into it.
Option 1 - Use a 'shared' intermediary workbook
You have an intermediary workbook that has data pasted into it from the Practice BA workbook and the Live BA workbook then reads that data.
Basically, once you've opened the main workbook from one instance of BA, you then open the extra workbook (call it middle.xlsx). Both workbooks are running in the same instance of Excel so you can push data into the 'middle.xlsx' book.
Then, from your second instance of BA, open it's own workbook. From that copy of Excel, open the same 'middle.xlsx' file.
You can push (paste) data into middle.xlsx and it will appear in the second copy of middle.xlsx so your Macros in your second BA workbook can read (copy) the data.
Bit convoluted, but workable. Middle.xlsx needs to be set as 'shared', and you'd need to add some VBA code with a timer into it so that it saved every X seconds (data is only added to the workbook from the 1st instance, when both workbooks are saved).
You need this intermediary workbook because if you opened the same copy of the BA workbook in shared mode, then the data put into it from BA would travel in both directions - getting very confusing.
Option 2 - Write the data to a file from the practice BA workbook and then open the file and read the data from your live BA workbook. You need to use error checking so that you handle contention (file can't be opened for reading, if it's also open for writing).
Option 3 - Use a Database. Same as option 2, but let the DB handle the locking.
An interesting problem to solve with a shared workbook, but in practice, you might find it easier to just write the data to a file.
Regards
Ian
* Well, I find it interesting anyway
Don't see why you should want to monitor in practice mode and switch to live when an event occured - I have automated marcos recording and assessing markets every 3 sec in the 10 min to event start - But it will only ever open a trade when conditions are met . - I also believe running practice mode for too long can incur grief from Betfair
Regards
G
Regards
G
-
gallybloke
- Posts: 41
- Joined: Sun Nov 01, 2009 1:25 pm
GMBing nobody has lost sight of the wood for the trees.GMBing wrote:To be fair, particually with complex excel sheets we can all lose sight of the woods for the trees
I know the chap who asked the original question they are using the sheets based on automating accumaltor betting based on a massive number of data they have gone through.
The pratice mode is used to count the number of races which are ran and the correct criteria is met rather than doubling there stakes each race through a live sheet which could run into the thousands in no time, once the specific number of races are ran and the criteria is met the live sheet then fires in the bet.
Gally