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.

 

[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.

 

[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.

 

[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)))

;-)