How to record data from Bet Angel spreadsheet

Discussion regarding the spreadsheet functionality of Bet Angel.
sa7med
Posts: 800
Joined: Thu May 18, 2017 8:01 am

workpeter wrote:
Fri Aug 11, 2017 9:44 pm
For those of you wishing to take your excel to the next level I would suggest you look at GetSystemTime in combination with worksheet change event. This is more granular than timers and doesnt cause your application to freeze.
Hey workpeter, would you be willing to elaborate more about how this could be done? Im using switesh's sheet which tends to freeze up and eat up resources. I'm a novice at VBA but am learning and getting better.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

the chart just gets pushed down the sheet as another row of data appears.
Select the chart, then on the ribbon under "Chart Tools", select "Format"

On the far right hand side you'll see a box giving the height and width. In the bottom right hand corner is a tiny box/arrow.

Click on this and you'll get a pop up box. Choose the properties tab and select "don't move or size with cells"
RonObvious
Posts: 198
Joined: Mon Dec 09, 2013 4:12 pm

Thanks NIgel this fixed the problem of the chart moving but unfortunately the range I've selected is still moving down under the data. I'm guessing it must be something to do with how the sheet is coded or something
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Excel updates any formulas/named range references when you insert/delete any rows/columns.

To overcome this when a new row/column is inserted, you need to update the chart so it still points to the original range.

On the attached on sheet 1 is a routine that calls the code in module 1 everytime a new row is inserted (row 2 only, change to suit or use the column ref instead).

You'll probably need to change the chart name if, like me, you've had half a dozen or more previous charts that you've deleted. Can easily get up to "Chart 20" or thereabouts!

If you're not sure of the name, select it and look at the name box (left of the formula bar below ribbon)
You do not have the required permissions to view the files attached to this post.
User avatar
workpeter
Posts: 165
Joined: Sat Jul 30, 2016 8:29 pm

sa7med wrote:
Mon Aug 14, 2017 12:29 pm
workpeter wrote:
Fri Aug 11, 2017 9:44 pm
For those of you wishing to take your excel to the next level I would suggest you look at GetSystemTime in combination with worksheet change event. This is more granular than timers and doesnt cause your application to freeze.
Hey workpeter, would you be willing to elaborate more about how this could be done? Im using switesh's sheet which tends to freeze up and eat up resources. I'm a novice at VBA but am learning and getting better.
The worksheet change event will trigger everytime BA communicates with excel. Even with streaming I don't get 20ms, more like 70ms, but thats still good. Within this routine use a conditional statement to compare the current system time to the captured system time. Pseudo code:

Begin Sub routine change event
IF CurrentSystemTime >= oldSystemTime + 100ms
{
//Do some code every 100 ms
//Then update global variable
OldSystemTime = CurrentSystemTime
}
End Sub Routine
User avatar
workpeter
Posts: 165
Joined: Sat Jul 30, 2016 8:29 pm

RonObvious wrote:
Mon Aug 14, 2017 11:58 am
Hi Workpeter,

I've been trying to do something similiar with the Bet Angel spreadsheet. I attached a copy of it that another forum user modified so that when its connected to excel and the 'Tick to record' function on the data sheet is enabled it copies data from Row 5 every second. I've tried to capture this data as its updating and populating rows below using fixed ranges and populate a line chart in real time but it wont work, the chart just gets pushed down the sheet as another row of data appears.

I thought something like this would be relatively simple but the only video I could find that shows what I'm trying to do is this one : https://youtu.be/xLTEnd1u5Eo

And that seems to suggest its very complex :?
I'm not at home so cannot open your spreadsheet fully. However our approaches are somewhat different. If you spend some time investigating my solution you will find it will not have the issues you describe. In a nutshell it's overwriting the same cells so nothing moves around distorting formula and positioning. Also there's nothing preventing you using a blend of both solutions. Your solution of adding rows of data will ensure you have an entire history and them elsewhere on the spreadsheet you use my solution which focuses on the last X number of iterations to which your graph points to.
RonObvious
Posts: 198
Joined: Mon Dec 09, 2013 4:12 pm

nigelk wrote:
Mon Aug 14, 2017 3:40 pm
Excel updates any formulas/named range references when you insert/delete any rows/columns.

To overcome this when a new row/column is inserted, you need to update the chart so it still points to the original range.

On the attached on sheet 1 is a routine that calls the code in module 1 everytime a new row is inserted (row 2 only, change to suit or use the column ref instead).

You'll probably need to change the chart name if, like me, you've had half a dozen or more previous charts that you've deleted. Can easily get up to "Chart 20" or thereabouts!

If you're not sure of the name, select it and look at the name box (left of the formula bar below ribbon)

Hi Nigel,

I'm unable to open the file, nothing happens when I double click it.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Needs to be unzipped, you should get a box pop up when you double click it?
RonObvious
Posts: 198
Joined: Mon Dec 09, 2013 4:12 pm

I've had to open excel and try to open the file from within Excel but I'm getting the attached pop up box.

Not sure what the issue is but I've tried re-installing MS Office and still nothing.
You do not have the required permissions to view the files attached to this post.
RonObvious
Posts: 198
Joined: Mon Dec 09, 2013 4:12 pm

Not sure if the file is corrupted or not but when I open it as you can see in the preview box of the screen grab I sent its just random symbols.
User avatar
mjmorris335
Posts: 180
Joined: Mon Jun 06, 2016 11:29 am

RonObvious wrote:
Tue Aug 15, 2017 1:20 pm
Not sure if the file is corrupted or not but when I open it as you can see in the preview box of the screen grab I sent its just random symbols.
Have you tried unzipping it? Right click the file in Windows File Explorer and select Extract All.

Mike
RonObvious
Posts: 198
Joined: Mon Dec 09, 2013 4:12 pm

Got it now thanks MIke
chens
Posts: 10
Joined: Mon Jul 10, 2017 10:17 am

Would there be any ready made excel spreadsheets that grab tennis scores as they happen from betfair & perhaps odds and save them to an excel file for analysis?
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”