Determining a value

[bknight]

I'm having difficulty in determining a value for one of my symbol. Attached is a table of values make no mistake there is no error here. There are 8 ticks between each minor division and each tick is worth 7.8125. You multiply the ticks by 7.8125 to obtain the value. I'm attempting to find a method of counting ticks in code to calculate a value with the code doing the counting. If the 8 ticks were uniformly dividing each minor division it would be a trivial solution, but as you may observe there is no .9 or .4 in the set. How would a code be written that can accurately count the ticks?

I'm posting this in Access but the same or similar code needs to work in Excel also.

[Gasman]

I'm having difficulty in determining a value for one of my symbol

I am having difficulty understanding what that even means?

So what are you looking for? a value < = Price?
You could add your missing rows and use 0 ticks, then DSum() for those <= your value?

[bknight]

I'm having difficulty in determining a value for one of my symbol

I am having difficulty understanding what that even means?

So what are you looking for? a value < = Price?
You could add your missing rows and use 0 ticks, then DSum() for those <= your value?

Perhaps I should have bolded the word counting.

I'm attempting to find a method of counting ticks with code. I'm not sure adding a 0 in ticks is the answer but I'll give it a try. The exercise is to count the ticks between two positions. Once a code is built calculation of a value is easy.
Now as an example the number of ticks from 3.0 to 4.0 is 8, the count from 3.7 to 4.0 is 2, the count from 3.3 to 4.0 is 5. Now I'm looking for code to count the ticks.

[Gasman]

If I was was to count all records from 3.3 to 4, I would 6 records?
So which are you excluding, Upper or Lower?? Likewise 3.3 to 4 is 3?,so you have to explicitly state the rules.

Regardless, if you are passing two parameters to a function (let's say 3.3 and 4) then use a DCount() if you leave it as it is, or a DSum() is you add the values that do not contain a tick record.

My initial thoughts were that you had a single value that you needed to see in what range it was and how many to the max(or min) of that range?
Again, you have to be explicit as to what is required.
I will *guess* at criteria > (or >=) your value and < (or <=) INT(YourValue)+1

Again, my thoughts were if you added those 'missing' records with a value of 0 ticks, then a DSum() would work, using your words 'if the 8 ticks were uniformly dividing each minor division it would be a trivial solution'

[bknight]

If I was was to count all records from 3.3 to 4, I would 6 records?
So which are you excluding, Upper or Lower?? Likewise 3.3 to 4 is 3?,so you have to explicitly state the rules.

Regardless, if you are passing two parameters to a function (let's say 3.3 and 4) then use a DCount() if you leave it as it is, or a DSum() is you add the values that do not contain a tick record.

My initial thoughts were that you had a single value that you needed to see in what range it was and how many to the max(or min) of that range?
Again, you have to be explicit as to what is required.
I will *guess* at criteria > (or >=) your value and < (or <=) INT(YourValue)+1

Again, my thoughts were if you added those 'missing' records with a value of 0 ticks, then a DSum() would work, using your words 'if the 8 ticks were uniformly dividing each minor division it would be a trivial solution'

The count from 3.3-4.0 is 5 records, 3.5.3.6.3.7.3.8.4.0; 3.3-4.0 is 5 records, 3.5,3.6,3.7.3.8,4.0. The table I posted is explicitly the rules. You are not allowed to add items, whether they have a 0 tick or not the count increases when you do that. Look carefully at the table there are 8 ticks between 3.0 and 4.0. The numbers missing are what I indicated in the first post, there is no .4 or .9. Think of the column labeled ticks, I added the 1 because each step in the set is 1. But the data doesn't contain the 1's that was just to demonstrate the courting. The image attached is how data would be viewed and used for the count, this is just a small set it could be much larger, for example 2.8-7.1(or even larger) what is the count in this case? I don't know I would have to visually count them, but that is what the code would be required to do, it would be 34, unless I made a mistake, (4*8+2). There never be a .4 or .9 in any data set. If that satisfies your description of rules, so be it.

[HansV]

How about

Code: Select all

Function CountTicks(varStart, varEnd)
    Dim i As Long
    If IsNull(varStart) Or IsNull(varEnd) Then
        CountTicks = Null
    Else
        For i = 10 * varStart + 1 To 10 * varEnd
            If i Mod 10 <> 4 And i Mod 10 <> 9 Then
                CountTicks = CountTicks + 1
            End If
        Next i
    End If
End Function

For example:

Code: Select all

Debug.Print CountTicks(3.3, 4)
 5 

[bknight]

How about

Code: Select all

Function CountTicks(varStart, varEnd)
    Dim i As Long
    If IsNull(varStart) Or IsNull(varEnd) Then
        CountTicks = Null
    Else
        For i = 10 * varStart + 1 To 10 * varEnd
            If i Mod 10 <> 4 And i Mod 10 <> 9 Then
                CountTicks = CountTicks + 1
            End If
        Next i
    End If
End Function

For example:

Code: Select all

Debug.Print CountTicks(3.3, 4)
 5 

That will work with modifications.
Thanks Hans

[bknight]

With Excel I have start and end values in column C with the final value in column O. How do I execute the function after the end value has been entered? There is a column for ActionID (L) and a description of those actions in column D. My normal procedure is to copy a previous row so I don't have to enter all the values(just Enter/Return key) and insert that at top of the data both start and end entries.

[HansV]

You write "start and end values in column C". Two values in one cell?

[bknight]

You write "start and end values in column C". Two values in one cell?

Different adjacent rows normally. This the current spreadsheet that needs to be CORRECTED.

[HansV]

Please explain in detail what you want to count. Keep in mind that I don't have the slightest idea what you're doing. Be as explicit as possible.

[bknight]

As I indicated the sheet needs to corrected. The value in c9 is/will be 102'02.8 the value in c10 is/will be 102'03.3 the value in c11 is/will be 102'03.0. Now the count is for one of them 3.3-2.8 and for the second one 3.0-2.8. The total count would be the sum of the two counts. Normally as I indicated there is only two adjacent rows to consider. For example IF row 9 is quantity 1(instead of two) then the count would be 3.3-2.8.

[HansV]

I cannot help you, sorry.

[bknight]

I cannot help you, sorry.

Take a one to one entering only row 9 and row ten and execute the function? You know I had a similar discussion with Chris Greaves(?) about how to execute a function a while back and I still don't know how to execute one.

[bknight]

I have an issues with my Access codes. Here is the code for MES and it works, updating the profit field.

Code: Select all

Function CalcProfit(Profit)
'This Function Will Calculate The Profit
Dim db As DAO.Database
Dim Rs As DAO.Recordset
Dim Fld1 As Field, Fld2 As Field, Fld3 As Field
Dim Fld4 As Field, Fld5 As Field, Fld6 As Field
Dim Fld7 As Field, Fld11 As Field, Fld12 As Field
Dim I As Long, intCurQty As Integer, intnextQty As Integer, intbackRec As Integer
Dim dblPrevAmt As Double, dblCurAmt As Double
Dim strCurSymbol As String, strCurCon As String
Set db = CurrentDb
Set Rs = db.OpenRecordset("Select * From Trades Order By ID")
Set Fld1 = Rs!ID
Set Fld2 = Rs!Tradedate
Set Fld3 = Rs!Symbol
Set Fld4 = Rs!ContractMonth
Set Fld5 = Rs!Quantity
Set Fld6 = Rs!ActionID
Set Fld7 = Rs!Price
Set Fld11 = Rs!Amount
Set Fld12 = Rs!Profit
Rs.MoveFirst
Rs.MoveLast
    strCurSymbol = Fld3
    strCurCon = Fld4
    intCurQty = Fld5
    dblCurAmt = Fld11
For I = Rs.RecordCount To 2 Step -1
    If I < Rs.RecordCount Then
        If Fld6 = 46 Or Fld6 = 48 Then
        'Find prev opening trades
            If Fld3 = strCurSymbol And Fld4 = strCurCon And IsNull(Fld12) Then 'If multiple openings, then place a zero in profit of closed opening trade
            'Found prev opening symbol and contract
                dblPrevAmt = Abs(intCurQty) * Fld11 / Abs(Fld5)
                Rs.MoveLast ' move to the closing trade
                Rs.Edit
                Fld12 = dblPrevAmt + dblCurAmt
                Rs.Update
                Exit For
            End If
        End If
    End If
Rs.MovePrevious
Next I
Set Rs = Nothing
Set db = Nothing
End Function

For the TU Calculation of profit is way different and does not work, have look at this and tell me why the profit field doesn't update

Code: Select all

Function CalcTUProfit(Profit)
'This Function Will Calculate The Profit for TU Only
Dim db As DAO.Database
Dim Rs As DAO.Recordset
Dim Fld1 As Field, Fld2 As Field, Fld3 As Field
Dim Fld4 As Field, Fld5 As Field, Fld6 As Field
Dim I As Long, J As Long, CountTicks As Long, intCurQty As Integer, intnextQty As Integer, intbackRec As Integer
Dim intCurWhle As Integer, intPrevWhle As Integer, intWhleMult As Integer
Dim dblPrevAmt As Double, dblCurAmt As Double, dblPrevFrc As Double, dblCurFrc As Double
Dim strCurSymbol As String, strCurCon As String, strCurRawP As String, strPrevRawP As String
Dim varStart As Variant, varEnd As Variant
Dim p As Double, p1 As Double
Set db = CurrentDb
Set Rs = db.OpenRecordset("Select * From Trades Order By ID")
Set Fld1 = Rs!ID
Set Fld2 = Rs!Tradedate
Set Fld3 = Rs!Symbol
Set Fld4 = Rs!ContractMonth
Set Fld5 = Rs!Quantity
Set Fld6 = Rs!ActionID
Set Fld7 = Rs!RawP
Set Fld11 = Rs!Amount
Set Fld12 = Rs!Profit
Rs.MoveFirst
Rs.MoveLast
    strCurSymbol = Fld3
    strCurCon = Fld4
    intCurQty = Fld5
    strCurRawP = Fld7 'Closing Price of TU
    p = InStr(strCurRawP, "'")
    intCurWhle = Left(strCurRawP, p - 1)
    dblCurFrc = Mid(strCurRawP, p + 1)
    varEnd = dblCurFrc
 For I = Rs.RecordCount To 2 Step -1
    If I < Rs.RecordCount Then
        If Fld6 = 46 Or Fld6 = 48 Then
        'Find prev opening trades
            If Fld3 = strCurSymbol And Fld4 = strCurCon And IsNull(Fld12) Then 'If multiple openings, then place a zero in profit of closed opening trade
            'Found prev opening symbol and contract
            strPrevRawP = Fld7
            p = InStr(strPrevRawP, "'")
            intPrevWhle = Left(strPrevRawP, p - 1)
            dblPrevFrc = Mid(strPrevRawP, p + 1)
            varStart = dblPrevFrc 'intPrevWhle + dblPrevFrc 'Example 102+02.1
            'Count the number of ticks between Start and End
            'For each whole number there are 256 ticks
            varEnd = Abs(intPrevWhle - intCurWhle) * 256 + dblCurFrc
                If varStart <= varEnd Then
                    For J = 10 * varStart + 1 To 10 * varEnd
                        If J Mod 10 <> 4 And J Mod 10 <> 9 Then
                            CountTicks = CountTicks + 1
                        End If
                    Next J
                Else
                    For J = 10 * varStart - 1 To 10 * varEnd Step -1
                        If J Mod 10 <> 4 And J Mod 10 <> 9 Then
                            CountTicks = CountTicks + 1
                        End If
                    Next J
                End If
                Rs.MoveLast ' move to the closing trade
                Rs.Edit
                If Fld5 > 0 Then
                    Fld12 = Fld5 * CountTicks * 7.8125 'dblPrevAmt + dblCurAmt
                Else
                    Fld12 = Fld5 * CountTicks * 7.8125
                End If
                Rs.Update
                Exit For
            End If
        End If
    End If
Rs.MovePrevious
Next I
Set Rs = Nothing
Set db = Nothing
End Function

Now the code works giving the correct value for profit but when the code goes through Rs.Update the field remains a null.

[bknight]

No one has an answer as to why a Rs.Edit followed by some calculation and then a Rs.Update fails to create a value in the profit field? I altered the code slightly to encompass more possible scenarios

Code: Select all

Function CalcTUProfit(Profit)
'This Function Will Calculate The Profit for TU Only
Dim db As DAO.Database
Dim Rs As DAO.Recordset
Dim Fld1 As Field, Fld2 As Field, Fld3 As Field
Dim Fld4 As Field, Fld5 As Field, Fld6 As Field
Dim I As Long, J As Long, CountTicks As Long, intCurQty As Integer, intnextQty As Integer, intbackRec As Integer
Dim intCurWhle As Integer, intPrevWhle As Integer, intWhleMult As Integer
Dim dblPrevAmt As Double, dblCurAmt As Double, dblPrevFrc As Double, dblCurFrc As Double, dblBuyP As Double, dblSellP As Double
Dim dblCurCom As Double, dblCurFee As Double, dblPrevCom As Double, dblPrevFee As Double
Dim strCurSymbol As String, strCurCon As String, strCurRawP As String, strPrevRawP As String
Dim varStart As Variant, varEnd As Variant
Dim p As Double, p1 As Double
Set db = CurrentDb
Set Rs = db.OpenRecordset("Select * From Trades Order By ID")
Set Fld1 = Rs!ID
Set Fld2 = Rs!Tradedate
Set Fld3 = Rs!Symbol
Set Fld4 = Rs!ContractMonth
Set Fld5 = Rs!Quantity
Set Fld6 = Rs!ActionID
Set Fld7 = Rs!RawP
Set Fld11 = Rs!Amount
Set Fld12 = Rs!Profit
Rs.MoveFirst
Rs.MoveLast
    strCurSymbol = Fld3
    strCurCon = Fld4
    intCurQty = Fld5
    strCurRawP = Fld7 'Closing Price of TU
    p = InStr(strCurRawP, "'")
    intCurWhle = Left(strCurRawP, p - 1)
    dblCurFrc = Mid(strCurRawP, p + 1)
    If Fld6 = 46 Or Fld6 = 47 Then
        dblBuyP = intCurWhle + dblCurFrc / 32
    Else
        dblSellP = intCurWhle + dblCurFrc / 32
    End If
    dblCurCom = -Abs(Fld5 * 1.5)
    dblCurFee = -Abs(Fld5 * 0.67)
    varEnd = dblCurFrc
 For I = Rs.RecordCount To 2 Step -1
    If I < Rs.RecordCount Then
        If Fld6 = 46 Or Fld6 = 48 Then
        'Find prev opening trade
            If Fld3 = strCurSymbol And Fld4 = strCurCon And IsNull(Fld12) Then 'If multiple openings, then place a zero in profit of closed opening trade
            'Found prev opening symbol and contract
            strPrevRawP = Fld7
            p = InStr(strPrevRawP, "'")
            intPrevWhle = Left(strPrevRawP, p - 1)
            dblPrevFrc = Mid(strPrevRawP, p + 1)
            dblPrevCom = -Abs(1.5 * Fld5)
            dblPrevFee = -Abs(0.67 * Fld5)
            If Fld6 = 46 Or Fld6 = 47 Then
                dblBuyP = intPrevWhle + dblPrevFrc / 32
            Else
                dblSellP = intPrevWhle + dblPrevFrc / 32
            End If
            varStart = dblPrevFrc 'Example 102+02.1/32
            'Count the number of ticks between Start and End
            'For each whole number there are 256 ticks
            varEnd = Abs(intPrevWhle - intCurWhle) * 256 + dblCurFrc
                If varStart <= varEnd Then
                    For J = 10 * varStart + 1 To 10 * varEnd
                        If J Mod 10 <> 4 And J Mod 10 <> 9 Then
                            CountTicks = CountTicks + 1
                        End If
                    Next J
                Else
                    For J = 10 * varStart - 1 To 10 * varEnd Step -1
                        If J Mod 10 <> 4 And J Mod 10 <> 9 Then
                            CountTicks = CountTicks + 1
                        End If
                    Next J
                End If
    'End If
                Rs.MoveLast ' move to the closing trade
                Rs.Edit
                If dblSellP > dblBuyP Then
                    Fld12 = Abs(Fld5) * CountTicks * 7.8125 - Abs(Fld5 * 1.5) - Abs(Fld5 * 0.67) - Abs(dblPrevCom) - Abs(dblPrevFee)
                Else
                    Fld12 = -Abs(Fld5) * CountTicks * 7.8125 - Abs(Fld5) * 1.5 - Abs(Fld5 * 0.67) - Abs(dblPrevCom) - Abs(dblPrevFee)
                End If
                Rs.Update
                Exit For
            End If
        End If
    End If
Rs.MovePrevious
Next I
Set Rs = Nothing
Set db = Nothing
End Function

When stepping through the code, a watchpoint on Fld12(Profit) indicates a value is present subsequent to the last If statement and following the Rs.Update, but that value is not present in the Profit field after the code finishes.

[Gasman]

I see Fld12 set to Rs!Profit.
I then see Fld12 being overwritten?

I do not see Fld12 repopulating rs!Proft ?

[bknight]

I see Fld12 set to Rs!Profit.
I then see Fld12 being overwritten?

I do not see Fld12 repopulating rs!Proft ?

Yes Fld12 is set to Rs!Profit
Fld12 is null at the start of the function, but yes the function writes the value calculated into Prodit, at least that is the intention.
Rs.Edit, prior to the function calculation "sets" up Fld12 for editing
Rs.Update after the calculation clears the dirty status of Fld12 thereby making the value in Field Profit. Except it doesn't update. Watching the code work while stepping through it, a value shows up in Fld12, it just never gets out of dirty and when the code finishes the field is still null.

[Gasman]

Fld12 is not part of the recordset though, so no rs.Edit is required?, you can change that to your hearts content over and over.

You only need rs.Edit for the actual fields that exist in the recordset.
Then you actually have to set any fields in that recordset, then issue a rs.Update.

Have you even looked up any help on the subject?

https://learn.microsoft.com/en-us/offic ... -recordset

[bknight]

Fld12 is not part of the recordset though, so no rs.Edit is required?, you can change that to your hearts content over and over.

You only need rs.Edit for the actual fields that exist in the recordset.
Then you actually have to set any fields in that recordset, then issue a rs.Update.

Have you even looked up any help on the subject?

https://learn.microsoft.com/en-us/offic ... -recordset

You didn't look at all of the code, obviously. Here are the declarations and sets.

Code: Select all

Function CalcProfit(Profit)
'This Function Will Calculate The Profit
Dim db As DAO.Database
Dim Rs As DAO.Recordset
Dim Fld1 As Field, Fld2 As Field, Fld3 As Field
Dim Fld4 As Field, Fld5 As Field, Fld6 As Field
Dim Fld7 As Field, Fld11 As Field, Fld12 As Field
Dim I As Long, intCurQty As Integer, intnextQty As Integer, intbackRec As Integer
Dim dblPrevAmt As Double, dblCurAmt As Double
Dim strCurSymbol As String, strCurCon As String
Set db = CurrentDb
Set Rs = db.OpenRecordset("Select * From Trades Order By ID")
Set Fld1 = Rs!ID
Set Fld2 = Rs!Tradedate
Set Fld3 = Rs!Symbol
Set Fld4 = Rs!ContractMonth
Set Fld5 = Rs!Quantity
Set Fld6 = Rs!ActionID
Set Fld7 = Rs!Price
Set Fld11 = Rs!Amount
Set Fld12 = Rs!Profit

As you can plainly see Fld12 is set to Rs!Profit. And to answer your question, yes I have looked for help. Again if you look an earlier post, there is another similar profit calculation that works.