Manipulating the incoming numbers

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
sherman
Posts: 33
Joined: Mon Mar 24, 2014 10:58 am

Hi
Is there anything I can do to the incoming numbers of a connected spreadsheet so that they can be manipulated with vba ?
For example the prices, LTP's or volume figures ? ..
If I refer to them with vba they are ignored, but if I type in the cells then the vba works, so I am seeing that there is a difference in figures coming from BA/BF as opposed to numbers you actually type in, is there a word or description for that?

I have tried using formulas like multiplying by one or adding zero to no avail ... is ther another way to make the incoming numbers "real" for want of a better expression?

Thanks for any help
Paul
deansaccount
Posts: 120
Joined: Mon May 30, 2016 5:19 pm

Just a thought (not tested) if BA is feeding a value into A1, if you set A2 value as =A1 then reference A2 in the VBA what happens?
sherman
Posts: 33
Joined: Mon Mar 24, 2014 10:58 am

thanks for your reply, however this dosn't work .. I tried (for example)
=A1,
=if(A1>0,A1,0)
=SUM(A1+0)
=SUM(A1*1)
Copied and pasted link ...

I am only a basic Excel user so there may me another way .. hopefully

cheers
Paul
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

How are you trying to fire off your VBA? Worksheet_change/calculate?

You can see how the data gets changed on the sheet using

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

Debug.Print Target.Address

End Sub
And then when you decide which block you want to trigger your code use something like

Code: Select all

If Not Intersect(Target, Range("C2")) Is Nothing Then
sherman
Posts: 33
Joined: Mon Mar 24, 2014 10:58 am

thanks for the reply .. i'm not too hot on vba but here is an example code ..

Code: Select all

Option Explicit

Dim OldVal
Public Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("H9:H16")) Is Nothing Then
    Application.EnableEvents = False
    OldVal = Target.Value
    Application.EnableEvents = True
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("H9")) Is Nothing Then
        Application.EnableEvents = False
        Range("AN9") = Target.Value - OldVal
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, Range("H10")) Is Nothing Then
        Application.EnableEvents = False
        Range("AN10") = Target.Value - OldVal
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, Range("H11")) Is Nothing Then
        Application.EnableEvents = False
        Range("AN11") = Target.Value - OldVal
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, Range("H12")) Is Nothing Then
        Application.EnableEvents = False
        Range("AN12") = Target.Value - OldVal
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, Range("H13")) Is Nothing Then
        Application.EnableEvents = False
        Range("AN13") = Target.Value - OldVal
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, Range("H14")) Is Nothing Then
        Application.EnableEvents = False
        Range("AN14") = Target.Value - OldVal
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, Range("H15")) Is Nothing Then
        Application.EnableEvents = False
        Range("AN15") = Target.Value - OldVal
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, Range("H16")) Is Nothing Then
        Application.EnableEvents = False
        Range("AN16") = Target.Value - OldVal
        Application.EnableEvents = True
    End If
End Sub
The idea would be to note the change in value (as the numbers change) in col H and put the answer in Col
AN
don't know if that'sthe best way to go about it, but it works in an excel sheet if you type numbers in Col H (9 to 16) ...
Cheers
Paul
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

Try running it in debug mode and watching the variables. (press f9 on the line you want to break at and then use F8 to step through your code, hover your mouse over a variable to see its value)

It might be that Target.Value isn't what you think it is, Target is a range and therefore not necessarily a single cell, esp as BA updates excel in chunks of cells at a time.

Oldval = Range("H9").Value or similar might work better for you seeing as you know the cell you want to look at.
sherman
Posts: 33
Joined: Mon Mar 24, 2014 10:58 am

thanks, I;ll have a look tomorrow now ..
Cheers
Paul
sherman
Posts: 33
Joined: Mon Mar 24, 2014 10:58 am

Shaun, i tried to run in debug but tbh I'm not knowledgable enough with vba to glean anything ...
The fact is that the code works fine on the BA sheet or a new blank sheet ...
ie if I type 24 into H9, then 24 will appear in AN9, then if I change H9 to 26, then AN9 will display 2 ..etc this works fine
But If I connect the sheet to BA and lets say H9 shows 24, nothing will appear in AN9, and nothing will happen when H9 changes ..
It seems that the numbers displayed on a "Connected" sheet arn't recognised ..
How would you best describe the situation ? are the numbers there via API stream ?

Appreciate any help, thanks
Paul
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

sherman wrote:
Tue Sep 25, 2018 9:31 am
Shaun, i tried to run in debug but tbh I'm not knowledgable enough with vba to glean anything ...
The fact is that the code works fine on the BA sheet or a new blank sheet ...
ie if I type 24 into H9, then 24 will appear in AN9, then if I change H9 to 26, then AN9 will display 2 ..etc this works fine
But If I connect the sheet to BA and lets say H9 shows 24, nothing will appear in AN9, and nothing will happen when H9 changes ..
It seems that the numbers displayed on a "Connected" sheet arn't recognised ..
How would you best describe the situation ? are the numbers there via API stream ?

Appreciate any help, thanks
Paul
It's nothing to do with the api. BA will probably use something called DDE (Dymanic data exchange) to talk to Excel.

Put an breakpoint on the line..OldVal = Target.Value.. by pressing f9.
When you code runs, execution will stop at that line.
Right click on "Target.Value" and select 'Watch'
What does it say the value of Target.Value is in the window at the bottom below your code?

Then each time you press f8 the code will move on to the next line it's going to execute. That is handy when you have 'If' lines as you can see if the execution steps into your conditional code or not.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Not sure exactly what you need.

1. Just copy any changes in col H to col AN?
2. Or store the values in col AN (oldval) before they update with the new vales from col H?
sherman
Posts: 33
Joined: Mon Mar 24, 2014 10:58 am

hi Shaun .. learning all the time here, thanks
Basically it sees OldVal as "empty"
So it's just not seeing the numbers from BA
cheers
paul
sherman
Posts: 33
Joined: Mon Mar 24, 2014 10:58 am

thanks nigelk
What I am trying to achieve in this instance is to monitor a col ie "H" and when the numbers change to have the new updated value minus the old value put into col AN ..

so if H9 is 5.8, then it changes to 6.0, then AN9 should show 0.2, if it then changes to 5.5 then AN9 should show -0.5 and so on .. and of course during in-play this needs to happen fast .. and continously ..

thanks
Paul
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

There's always several ways to code a problem, but just to get something working you could do something like this code fragment. If you have trouble with code it's often best to strip it back to something super simple, if not expecially efficient, and work up from there adding sophistication.

The biggest potential problem I see is that the refresh rate is so fast that your figures in col AN (the price change?) will always appear to be zero. With that in mind the code below has an additional 'IF' which will mean it only updates if the price has changed, basically freezing the last change figure. Just remove the If & End If statements if you don't want it to work like that.

This code is inefficient as it will be called when anything changes anywhere, but as I said...let's get something working and build it up.

(I haven't tried this so E&OE)

Code: Select all

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
        
        'Disable event monitoring so that changes don't retrigger Worksheet_Change
        Application.EnableEvents = False
  
  	'Now update the price change figure if the price has changed,  H = New prices, AN = price changes, AO = Old prices
       
        If Range("AO9").Value <> Range("H9").Value  then
		
		'Store the change from the old saved price
		Range("AN9").Value =  Range("AO9").Value - Range("H9").Value	
	        
	        'Store current price as the old price
       	        Range("AO9").Value  = Range("H9").Value 					
	End if

        If Range("AO10").Value <> Range("H10").Value  then
		
		'Store the change from the old saved price
		Range("AN10").Value =  Range("AO10").Value - Range("H10").Value	
	        
	        'Store current price as the old price
       	        Range("AO10").Value  = Range("H10").Value 					
	End if
	
	...etc
	
	'Enable event monitoring so that changes trigger Worksheet_Change
        Application.EnableEvents = True
  
End Sub
Try that.

Next step would be to rename this proceedure eg replace "Worksheet_Change(ByVal Target As Range)" with something like 'UpdatePriceChanges()" and then insert some more code to call this only when your specific column changes. eg.

Code: Select all

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
	If Not Intersect(Target, Range("H9:H16")) Is Nothing Then
    		Call UpdatePriceChanges
	End If
End Sub
It's not pretty and it's not clever but it might get you moving. As I said it's untested so I hope it works.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

I think the flaw in your logic came down to this...

OldVal = Target.Value
code code code code code
Range("AN9") = Target.Value - OldVal

It would always be zero.

It's sometimes just a matter of writing down first how you'd do it on paper.
eg
Look at the new price
Is is different to the price I wrote down earlier?
If so, calc the change
Write down the 'old' price so I can remember it later
Repeat

...what was missing was writing down the old price to compare with the new one.

All coding is like that really, just re-doing steps that used to be done manually in ledgers. The problem people have though is that they discount steps which are done subconsciously such as remebering previous things, which of course a computer needs to be told to do. Basically you have to treat a computer like an idiot and tell it everything. Don't tell it what you do, tell it how you think, if that makes sense.
sherman
Posts: 33
Joined: Mon Mar 24, 2014 10:58 am

Thanks VERY much Shaun for your time and understanding .. this gives me plenty to chew over ..
Cheers
Paul
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”