Determining a value
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Determining a value
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.
I'm posting this in Access but the same or similar code needs to work in Excel also.
You do not have the required permissions to view the files attached to this post.
-
- 2StarLounger
- Posts: 117
- Joined: 22 Feb 2022, 09:04
Re: Determining a value
I am having difficulty understanding what that even means?I'm having difficulty in determining a value for one of my symbol
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?
Using Access 2007/2019.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Determining a 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.
-
- 2StarLounger
- Posts: 117
- Joined: 22 Feb 2022, 09:04
Re: Determining a value
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'
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'
Using Access 2007/2019.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Determining a value
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.Gasman wrote: ↑18 Jun 2023, 17:45If 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'
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Determining a value
How about
For example:
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
Code: Select all
Debug.Print CountTicks(3.3, 4)
5
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Determining a value
That will work with modifications.HansV wrote: ↑19 Jun 2023, 06:28How about
For example: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
Code: Select all
Debug.Print CountTicks(3.3, 4) 5
Thanks Hans
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Determining a value
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.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Determining a value
You write "start and end values in column C". Two values in one cell?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Determining a value
Different adjacent rows normally. This the current spreadsheet that needs to be CORRECTED.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Determining a value
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.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Determining a value
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.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Determining a value
I have an issues with my Access codes. Here is the code for MES and it works, updating the profit field.
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
Now the code works giving the correct value for profit but when the code goes through Rs.Update the field remains a null.
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
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
You do not have the required permissions to view the files attached to this post.
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Determining a value
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
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.
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
-
- 2StarLounger
- Posts: 117
- Joined: 22 Feb 2022, 09:04
Re: Determining a value
I see Fld12 set to Rs!Profit.
I then see Fld12 being overwritten?
I do not see Fld12 repopulating rs!Proft ?
I then see Fld12 being overwritten?
I do not see Fld12 repopulating rs!Proft ?
Using Access 2007/2019.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Determining a value
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.
-
- 2StarLounger
- Posts: 117
- Joined: 22 Feb 2022, 09:04
Re: Determining a value
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 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
Using Access 2007/2019.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Determining a value
You didn't look at all of the code, obviously. Here are the declarations and sets.Gasman wrote: ↑21 Jun 2023, 14:59Fld12 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
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