multiplying values from different rows

[Tskutnik]

Hi all, this is an old thread I need to revisit. Slighty revised.

For matching accounts and securities: I need to calculate the difference in a stock price from one day to another, and create a cumulative return over time. I get that this is more of an excel-style function, but I know it is possible in Access with some "on open" or "on click" code in a form. I just don't know how to do it.
This would be an append query, so the results (along with other fields) would post to an existing table called "Results1". Im good with the append process, I just need the query to create the results.

Attached is a spreadsheet with the formulas in column D. I can also post a small sample DB if that helps.

In VBA I could set the value in the beginning date row to a constant, and use that value in the formula for the ending date row, but I don't know how to translate this to access. I looked everywhere I can think of online and can't find the method.

As always, I really appreciate the help.

 

[The_Doc_Man]

Just as a side note here, nothing was attached to your message.

 

[arnelgp]

This would be an append query, so the results (along with other fields) would post to an existing table called "Results1". Im good with the append process, I just need the query to create the results.

you don't need another table for the result.
what you need is an Aggregate (Total) query.

 

[Gasman]

Have a look at http://allenbrowne.com/subquery-01.html

 

[Tskutnik]

Just as a side note here, nothing was attached to your message.

Strange. Im posting a DB so maybe that will work better. ty

 

[Tskutnik]

Have a look at http://allenbrowne.com/subquery-01.html

This gets me in the right direction, thank you very much. Still not sure how to get this to really work so in a few minutes I'm posting a DB with some questions.

 

[Pat Hartman]

What do you want to do with the results? If you want a report, then that is the simplest solution. Reports are sequential so the records are in the order you need to do the calculation and you can save the current value so you can do the calculation very easily in the on Format event.

You can of course use the suggested query but if the target is a report, just do the calculation in the report.

The third option is a DAO code loop. Again, the data is sequential and so by saving the current value, you have what you need to do the running calculation. I guess if you wanted to store the result, this would be the best option but otherwise, it is the worst.

 

[Tskutnik]

Hi all, sorry for the delayed response. I was trying to figure this out without bothering all of you, but no dice. The concept is kind of simple, but I need help with the execution.

Requirement: Calculate a running total of values from a table using values from multiple rows, and paste those results back into the table in a dedicated column.
My STRONG preference is a query (not a form) that will populate the table when run.

The conceptual trick is multiplying the active rows' [daily_unitized_return] value by the prior day's row's (by [asof_date]) [daily_unitized_return]. There are some other conditions in the formula below, but that's the idea.

Attached is a database with one [Soure_Data] table of values.

In english, the code would do something like:

1) Sort the table by a) [account], b) [security], c) [asof_date] (there is no guarantee the records will be in the correct order, although they are in the attached DB).

2) loop through each row, calculating each active row's [cumulative_unitized_return] using the following formula (in english) =
IF
[account] value in active row <> [account value] in row above OR [security] in active row <> [security value] in row above,
THEN
[daily_unitized_return] in active row,
ELSE
([daily_unitized_return] in active row * [cumulative_unitized_return] in row above)).
This formula is also in the table's field description.

3) Paste each result into the active row's [cumulative_unitized_return] field in the table.

The results should match the [EXPECTED_RESULT] column in the table, that I calculated in XL and pasted.

I've seen this work in a form, giving run-time results, but can't figure out how this works as described above. Don't worry too much about error checking, etc. I appreciate the help and don't want to make any additional work for anyone.

I really appreciate any help.

 

[ebs17]

SELECT 
   D.ID, 
   D.account, 
   D.asof_date, 
   D.security, 
   D.daily_unitized_return, 
   D.expected_result, 
   QueryProduct(D.ID, 1, D.daily_unitized_return, D.account, D.security) AS cumulative_unitized_return 
INTO 
   NewTable 
FROM 
   SourceData AS D 
WHERE 
   Reset_Globals() = True 
ORDER BY 
   D.security, 
   D.account, 
   D.asof_date

Due to the use of public variables in the QueryProduct function, the query result in a select query is very unstable when iterating through records. Therefore, it is useful to fix the query result in a new table using a make table query as used above. Instead of the make table query, an append query is of course also suitable for fixing.

' mod_QueryProduct

Public gQI_Category As String
Public gQI_Product As Double

Public Function QueryProduct(ByVal Init As Variant, ByVal StartValue As Long, _
        ByVal ActualValue As Variant, ParamArray Categories() As Variant) As Double
    Dim k As Long, v As Variant
    Dim sAllCategories As String

    v = Init
    If IsMissing(Categories) Then
        sAllCategories = "$$$$$"
    Else
        For k = 0 To UBound(Categories)
            If IsNull(Categories(k)) Then Categories(k) = vbNullString
        Next
        sAllCategories = Join(Categories, "|")
    End If

    If gQI_Category = sAllCategories Then
        gQI_Product = gQI_Product * Nz(ActualValue, 1)
    Else
        gQI_Category = sAllCategories
        gQI_Product = StartValue * Nz(ActualValue, 1)
    End If
    QueryProduct = gQI_Product
End Function

Public Function Reset_Globals() As Boolean
    gQI_Category = vbNullString
    gQI_Product = 0
    Reset_Globals = True
End Function

 

[Tskutnik]

SELECT
   D.ID,
   D.account,
   D.asof_date,
   D.security,
   D.daily_unitized_return,
   D.expected_result,
   QueryProduct(D.ID, 1, D.daily_unitized_return, D.account, D.security) AS cumulative_unitized_return
INTO
   NewTable
FROM
   SourceData AS D
WHERE
   Reset_Globals() = True
ORDER BY
   D.security,
   D.account,
   D.asof_date

Due to the use of public variables in the QueryProduct function, the query result in a select query is very unstable when iterating through records. Therefore, it is useful to fix the query result in a new table using a make table query as used above. Instead of the make table query, an append query is of course also suitable for fixing.

' mod_QueryProduct

Public gQI_Category As String
Public gQI_Product As Double

Public Function QueryProduct(ByVal Init As Variant, ByVal StartValue As Long, _
        ByVal ActualValue As Variant, ParamArray Categories() As Variant) As Double
    Dim k As Long, v As Variant
    Dim sAllCategories As String

    v = Init
    If IsMissing(Categories) Then
        sAllCategories = "$$$$$"
    Else
        For k = 0 To UBound(Categories)
            If IsNull(Categories(k)) Then Categories(k) = vbNullString
        Next
        sAllCategories = Join(Categories, "|")
    End If

    If gQI_Category = sAllCategories Then
        gQI_Product = gQI_Product * Nz(ActualValue, 1)
    Else
        gQI_Category = sAllCategories
        gQI_Product = StartValue * Nz(ActualValue, 1)
    End If
    QueryProduct = gQI_Product
End Function

Public Function Reset_Globals() As Boolean
    gQI_Category = vbNullString
    gQI_Product = 0
    Reset_Globals = True
End Function

EB - WOW! Perfect. Thanks very much, it's exactly what I needed. You saved me countless hours on the details for this. Greatly appreciated.

 

[Pat Hartman]

If the data changes or you enter it out of order, you have no choice but to use a query (I question the need to save the results though if you are using a query). If old data does not change and you cannot enter data out of order, then if you insist on saving the calculated value, you would do it as you save the record. You simply use a dSum() of the existing data, with criteria if there are multiple sums, then take that value and place it in a locked control. The sum saves when you save the record. I would ALWAYS lock this control since a user can NEVER change the calculated value.

The reason we don't save calculated values is because if any of the underlying data changes, the calculated value becomes invalid.

You still never said what you were going to do with the data. All you said was that you didn't want to use a form. Well I'm not sure how you are going to trigger the query or make table suggested by @ebs17 if not by a button on a form.

As I said, if this is for a report, you would calculate the value on the fly by simply setting the RunningSum property of a control to OverAll. No code is required. No special queries. Just a property setting.

 

[ebs17]

calculate the value on the fly by simply setting the RunningSum property

That's no use here, since it's a calculation with recursion - something that Jet-SQL can't do.
Of course, it would also be an option to switch to a database management system whose SQL dialect supports recursion.

 

[Pat Hartman]

That's no use here, since it's a calculation with recursion - something that Jet-SQL can't do.

There is no recursion in calculating a running sum in a report. It is a sequential operation. One pass through the recordset. AND, I am not arguing against doing it with a query, only suggesting if the use of the resulting sum is a report, then it should be done by the report rather than by the database engine. The OP has NEVER identified the purpose of calculating the running sum, only that he wanted to store it. Since we know that storing calculated values such as this is wrong or at best dangerous, I'm at least trying to get him to think about what he wants to do with the result.

 

[ebs17]

The numbers and the description tell me that these are percentage changes in prices per day. So it's not a running sum, but a running product, which then represents the combined compound interest effect.
When using it, I would speculate that one need daily values if one want to display and illustrate trends in diagrams and need tables / queries as a basis for these.

Unfortunately, I have not yet found an elegant way to transfer a recordset / form recordset directly into a database table.
Transferring individual values using a loop is not elegant, CopyFromRecordset to Excel or Getstring to Textfile for linked / importable tables are not direct.

 

[Josef P.]

Product of records = Exp(Sum(Log(x)))

;-)

 

[Pat Hartman]

Unfortunately, I have not yet found an elegant way to transfer a recordset / form recordset directly into a database table.

The "elegant" solution is to do it when the following days data is entered as I suggested in my first post. Of course if there is existing data, then you need to calculate the differences for the old data and save it. Going forward, the calculation should be done by the data entry form.