Struggling to get started

[Royville]

Hi all,

I have only recently got started with BA and I thought I would soon get my head around programing in excel, but the more I try to learn the more confused I get and basically I'm going nowhere fast. I'm really struggling with 'events' and where to put the code and stuff like that.

This is what I'm trying to do - I'm laying dogs.
When my bet wins (I loose) - I want the spreadsheet to divide the loss by the number of remaining races and add that portion to each race thereafter. A simple task I thought, but I just cant seem to get anywhere with it - Can anyone help me please, or start me off, or anything really.

Regards to all
Royville

[sjaak1943]

PM me and I will help you.

Sjaak

[Roger]

Sjaak, this may appear to be very helpful of you, but in fact offering to help via PM is against the principal of a forum. Firstly it stops anyone else gaining from your help, which another new person could search for. And secondly, it stops anyone else wanting to offer advice to the original poster possibly adding improvements.

[mak]

good point there Roger

[sjaak1943]

Sjaak, this may appear to be very helpful of you, but in fact offering to help via PM is against the principal of a forum. Firstly it stops anyone else gaining from your help, which another new person could search for. And secondly, it stops anyone else wanting to offer advice to the original poster possibly adding improvements.

Maybe you are right, but he's not struggling with BA, but with Excel.
That's why I did it this way.

Sjaak

[Royville]

Hi folks,

I don't want to cause any friction here, - I need you guys.

Regards
Royville

[Roger]

Royville, Sjaak, I do hope I haven't hindered the help process - I had my ex-moderator's hat on.

Ideally, you would post both sides of your correspondence on here, rather than as PMs. I'm sure Gary would not mind if it became a long thread. Certainly it doesn't matter if it's Excel help rather than BA help, the two are so closely linked, and I'm sure there are many people who could learn something.

[Royville]

Hi,

I do hope I haven't hindered the help process

Me too - At the moment I'm still clueless and without help!

Maybe what I thought to be a simple task is not so simple after all.

I'll just wait and hope...

Regards
Royville

[mak]

I haven't got a clue about VBA, but i suggest to be more specific in order someone here understand what you are trying, & where exactly you are stacked

You could post your code here & i am almost certain that you will find help

Cheers

* i also recommend the search facility there are tones of helpful code already here

[Royville]

Ok, heres where I'm up to,- (I put this code into sheet1 in the VBAProject box)

Code: Select all

Private Sub Worksheet_Calculate()

'Has a race loaded?
    If Range("W11").Value <> Range("U11").Value Then
        Range("W11").Value = Range("U11").Value
        'update STAKE cells
        Range("S5").Value = (Range("W12").Value + Range("W13").Value)
        Range("S6").Value = (Range("W12").Value + Range("W13").Value)
        Range("S7").Value = (Range("W12").Value + Range("W13").Value)
        Range("S8").Value = (Range("W12").Value + Range("W13").Value)
        Range("S9").Value = (Range("W12").Value + Range("W13").Value)
        Range("S10").Value = (Range("W12").Value + Range("W13").Value)
    Else
        ' check P/L column for losses
        If Range("X13").Value <> 0 Then
            If Range("X13").Value > 0 Then Range("W15").Value = (Range("W15").Value + Range("X13").Value)
            'Add (loss/remaining races) to the loss recover cell, update profit cell
            If Range("X13").Value < 0 Then
                Range("W13").Value = Range("W13").Value + ((Range("X13").Value * -1) / Range("W11").Value)
                Range("W15").Value = (Range("W15").Value + Range("X13").Value)
                'update STAKE cells
                Range("S5").Value = (Range("W12").Value + Range("W13").Value)
                Range("S6").Value = (Range("W12").Value + Range("W13").Value)
                Range("S7").Value = (Range("W12").Value + Range("W13").Value)
                Range("S8").Value = (Range("W12").Value + Range("W13").Value)
                Range("S9").Value = (Range("W12").Value + Range("W13").Value)
                Range("S10").Value = (Range("W12").Value + Range("W13").Value)
            End If
           
        End If
   
     End If

End Sub

Explaination: - how I hope it will work!!!
Look at the image of my spreadsheet first . . .

How do I upload an image ? ? ?

The first part of the if statement checks to see if cells W11 and U11 are different. If they are then a new race has loaded so W11 is made the same as U11.
U11 just tracks cell J3 (which is updated by BA) and changes value 'F' to 99.9 and value 'L' to 1. 'F' and 'L' denote the first and last races. W11 will be the value by which any loss is divided. Cells S5 to S10 are updated using W12 (users Stake) plus W13 (any loss to be recovered).
The else part of the statement checks for any gains or losses using X13 which is the sum of the P/L column. Any profit is added to cell W15 (the profit cell). A loss is first divided by cell W11 (the number of remaining races) then added to the recovery cell W13. The profit cell W15 also has the loss added to it. Finaly the Stake cells are updated.

This is all of my code - have I put it in the right place? - what else do I need to do? - the spreadsheet is a modified version of the Lay_Favorite.xls which I downloaded from this forum.
Regards
Royville

[osknows]

Hi Royville,

The code looks fine at first glance, are you having problems with it? It should be placed in the SHEET object of the VBA editor.

You are using an event called Private Sub Worksheet_Calculate() - i.e the event fires when any values change following a re-calculation

The more common way is to place your BA code in an event called worksheet_change which fires the event as soon as a cell changes.

Place this in the SHEET object as before

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

 
If Target.Columns.Count = 16 Then
Application.EnableEvents = False


'Has a race loaded?
    If Range("W11").Value <> Range("U11").Value Then
        Range("W11").Value = Range("U11").Value
        'update STAKE cells
        Range("S5").Value = (Range("W12").Value + Range("W13").Value)
        Range("S6").Value = (Range("W12").Value + Range("W13").Value)
        Range("S7").Value = (Range("W12").Value + Range("W13").Value)
        Range("S8").Value = (Range("W12").Value + Range("W13").Value)
        Range("S9").Value = (Range("W12").Value + Range("W13").Value)
        Range("S10").Value = (Range("W12").Value + Range("W13").Value)
    Else
        ' check P/L column for losses
        If Range("X13").Value <> 0 Then
            If Range("X13").Value > 0 Then Range("W15").Value = (Range("W15").Value + Range("X13").Value)
            'Add (loss/remaining races) to the loss recover cell, update profit cell
            If Range("X13").Value < 0 Then
                Range("W13").Value = Range("W13").Value + ((Range("X13").Value * -1) / Range("W11").Value)
                Range("W15").Value = (Range("W15").Value + Range("X13").Value)
                'update STAKE cells
                Range("S5").Value = (Range("W12").Value + Range("W13").Value)
                Range("S6").Value = (Range("W12").Value + Range("W13").Value)
                Range("S7").Value = (Range("W12").Value + Range("W13").Value)
                Range("S8").Value = (Range("W12").Value + Range("W13").Value)
                Range("S9").Value = (Range("W12").Value + Range("W13").Value)
                Range("S10").Value = (Range("W12").Value + Range("W13").Value)
            End If
           
        End If
   
     End If


Application.EnableEvents = True
End If


'If Target.Columns.Count = 16' - ensures the code will only run when BA updates excel rather than you changing the odd cell.

'Application.EnableEvents = False' - ensures that events are turned off while your code runs so that a never ending loop doesn't occur when you write back to the sheet triggering the event again....

Os

[Royville]

Hi Os,

Thanks for looking at my code, Much appreciated.
I think what you said regarding placement makes good sense. Knowing where to put the code exactly and how to run it was a great stumbling block for me, I was getting loops (intermittently) when certain cells updated, so I will try as you suggested and post back how I get on.
Many thanks
Royville.

[Royville]

Hi,

I ran the code yesterday and it did work much better (thanks to 'Osknows').
But I still have a problem. I was using the 'sum' of the 'Profit/Loss' column to work out loss recovery but it seems this column shows potential profit and loss for all runners in the race when a bet is placed, and every time the sheet was updated (every second or so) it recalculated and added more to the loss recovery cell.
I've managed to sort the recalculating by re-arranging the code . . .

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Columns.Count = 16 Then
        Application.EnableEvents = False
       
           
       
    'Has next race loaded?
    If Range("W11").Value <> Range("U11").Value Then
        Range("W11").Value = Range("U11").Value
       
        ' check P/L column for losses
        If Range("X13").Value <> 0 Then
            If Range("X13").Value > 0 Then Range("W15").Value = (Range("W15").Value + (Range("X13").Value * 0.95))
           
            'Add (loss/remaining races)to the loss recover cell, update profit cell
            If Range("X13").Value < 0 Then
                Range("W13").Value = Range("W13").Value + ((Range("X13").Value * -1) / Range("W11").Value)
                Range("W15").Value = (Range("W15").Value + Range("X13").Value)
               
                'update STAKE cells
                Range("S5").Value = (Range("W12").Value + Range("W13").Value)
                Range("S6").Value = (Range("W12").Value + Range("W13").Value)
                Range("S7").Value = (Range("W12").Value + Range("W13").Value)
                Range("S8").Value = (Range("W12").Value + Range("W13").Value)
                Range("S9").Value = (Range("W12").Value + Range("W13").Value)
                Range("S10").Value = (Range("W12").Value + Range("W13").Value)
            End If
        End If
    Else

        'check time to off
        If Range("R16").Value < 0 Then
            Range("Q16").Value = 0
        Else

            'when its time, LAY
            If Range("R16").Value <= Range("W16").Value Then
                Range("Q16").Value = 1
            Else
                Range("Q16").Value = " "
            End If
        End If
       
    End If
       
       
       
       
        Application.EnableEvents = True
    End If
   
 
 
 
   
End Sub

. . . but my question now is where do I obtain result data from so I can calculate a loss recovery when the result is known? I think I could use the 'Balance' cell I2, but is there a better way?

Regards to all
Royville

[osknows]

I know some people look up the results page as you are doing. I prefer to just use the balance and exposure figures. EG cells

I2 - Balance: 244.98
L2 - Exposure: -22.21

Your total bank = I2 - L2
= 267.19

You can either
a) have a starting bank to enter each day and compare to calc above to work out profit/loss (eg see example of this here http://gruss-software.co.uk/forum/viewt ... 9&start=21)

b) use VBA to compare the figure and update a field - eg one setup I use has a maximum bank achieved which increases with profit but will only decrease if loss is greater than a fixed percentage. It's essentially doing the same as a) but a worksheet_calculate event performs the check and updates the max bank.

The only drawback to this is you have to wait until the bets are settled before the bank updates so potentially there can be a 5-15 minute window where bank is not accurate. I'm quite happy with this as it doesn't really affect me.

In order for the above to work you need to place a U in cell J2 to update balance and exposure. I include the line below in my code

ThisWorkbook.Sheets(Target.Worksheet.Name).Range("J2").Value = "U" 'updates balance and exposure

[Royville]

Hi,

I know some people look up the results page as you are doing.

That wasn't what I was doing, but I think it is a more elegant solution - so thats the way I've gone too.
On the results sheet (Sheet3), cell (K1)now keeps a count of the results using COUNT, then the code checks this value using a 'NewResult' variable and acts accordingly.

My code now looks like this . . .

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewResult As Integer, OldResult As Integer



    If Target.Columns.Count = 16 Then
        Application.EnableEvents = False
                 
    'Has next race loaded?
    If Range("W11").Value <> Range("U11").Value Then
        Range("W11").Value = Range("U11").Value
       
        ' check results and update P/L ---------------------------------------------
               
        NewResult = Sheet3.Cells(11, 1)
        If OldResult <> NewResult Then
       
            If Sheet3.Cells(6, 2) = "RESULT_WON" Then
                Range("W15").Value = (Range("W15").Value + (Sheet3.Cells(4, 2) * 0.95))
                OldResult = NewResult
            End If
           
            'Add (loss/remaining races)to the loss recover cell, update profit cell
            If Sheet3.Cells(6, 2) = "RESULT_LOST" Then
                Range("W13").Value = Range("W13").Value + (Sheet3.Cells(4, 2) / Range("W11").Value)
                Range("W15").Value = (Range("W15").Value - Sheet3.Cells(4, 2))
                OldResult = NewResult
               
                'update STAKE cells
                Range("S5").Value = (Range("W12").Value + Range("W13").Value)
                Range("S6").Value = (Range("W12").Value + Range("W13").Value)
                Range("S7").Value = (Range("W12").Value + Range("W13").Value)
                Range("S8").Value = (Range("W12").Value + Range("W13").Value)
                Range("S9").Value = (Range("W12").Value + Range("W13").Value)
                Range("S10").Value = (Range("W12").Value + Range("W13").Value)
            End If
           
            If Sheet3.Cells(6, 2) = "RESULT_NOT_AVAILABLE" Then
                'do nothing
            End If
       
           
           
        End If '-----------------------------------------------------------------------
    Else
        'check time to off
        If Range("R16").Value < 0 Then
            Range("Q16").Value = 0
        Else
            'when its time, LAY
            If Range("R16").Value <= Range("W16").Value Then
                Range("Q16").Value = 1
            Else
                Range("Q16").Value = " "
            End If
        End If
       
    End If
   
    'Send Trigger to update Results
    'Range("J2").Value = "U"
    'Range("Q2").Value = "-6"
       
       
        Application.EnableEvents = True
    End If
   
 
 
 
   
End Sub


I'll try this out today and let you know how it goes.

By the way, - I hope its ok to post the code on the forum like this. I don't mind anybody copying/using it (at your own risk!), - and I would welcome and appreciate any comments or suggestions anyone may have.

Regards
Royville