Running Total Query

[Plender]

I am relatively new to Access and, for context, I am currently building a database to track my investment portfolio. One thing that I am looking to track is the adjusted cost base for each security for income tax purposes. The problem I am currently having is that the adjusted cost base is a running total and is dependent on previous "Buy" and "Sell" transactions and the sequence they occurred in.

The table for my transaction data looks something like this:

TransactionID	Security	TransactionType	TransactionDate	Quantity	Price	Commission
1	ABC	Buy	01-Jan-17	400	$20.00	$50.00
2	ABC	Buy	02-Jan-17	100	$19.00	$50.00
3	ABC	Buy	03-Jan-17	100	$21.00	$50.00
4	ABC	Sell	04-Jan-17	300	$22.50	$50.00
5	ABC	Buy	05-Jan-17	100	$22.00	$50.00
6	ABC	Sell	06-Jan-17	300	$23.00	$50.00

I'm looking for a query that will generate something similar to the following:

ACBDate	TotalHoldings	ACBChange	ACB
01-Jan-17	400	$8,050.00	$8,050.00
02-Jan-17	500	$1,950.00	$10,000.00
03-Jan-17	600	$2,150.00	$12,150.00
04-Jan-17	300	($6,075.00)	$6,075.00
05-Jan-17	400	$2,250.00	$8,325.00
06-Jan-17	100	($6,243.75)	$2,081.25

For reference:

• TotalHoldings is a running total of Quantity bought less Quantity sold
• ACBChange
  
  • For a "Buy" transaction is: Quantity * Price + Commission
  • For a "Sell" transaction is: -1 * ((Prior Period ACB) / (Prior Period TotalHoldings) * Quantity)
  
  
• ACB is a running total of ACBChange

I've tried using the DSUM function and I have something that seems to be working, but takes >10 seconds to calculate despite having only approximately 200 transactions. I feel like this is something I could build in Excel which would take less than a second to calculate, so I assume there's a better way. Does anyone have a suggestion on how you might approach this differently? I have also tried using subqueries, but I am getting a circular reference error because the ACB when it is a "Sell" transaction is dependent on the Total ACB in the prior period which is dependent on if there were previous "Sell" transactions.

[CJ_London]

the nature of what you are trying to do is recursive because of the nature of the sell calculation (which incidentally shouldn't you be adjusting for the sell commission?)

Access sql doesn't have recursive capabilities although it can be mimicked with multiple queries if you know how many levels you need to go down - and my guess this would not be the case.

If your backend is sql server, it can be done because tsql has recursive capabilities. In Access you can also do it by creating a vba function - or perhaps a normal one.

If you want to go the vba route the function would need to

have passed a parameters the security and trandate
would open a recordset something like "SELECT * FROM tranTable WHERE security= '" & security & "' AND trandate<" & trandate
then step through each record and calculate and accrue the relevant figures

However I don't have a quick fix for you other than this may be one of those rare situations where you need to store a calculated value at the time the record is entered. Or to pass the data to excel and do it there

[Plender]

Thanks Ajax, I was starting to get the feeling that might be the case.

[CJ_London]

this probably needs more work, but this works on the example data you provided




The Query

SELECT TranData.Security, TranData.TranDate, fTtlHolding([Security],[Trantype],[Quantity]) AS TtlHolding, facbChange([security],[trantype],[quantity],[price],[commission]) AS ACBChange, facb([security],[trantype],[quantity],[price],[commission]) AS ACB
FROM TranData
ORDER BY TranData.Security, TranData.TranDate

Uses 3 UDF's here - paste into a new module

Note the use of the static variable declaration which accumulates the values. The problem with the static values is they will keep on accumulating - click on a query row and the values will change. So, strongly recommend you make the above sql a recordsource to a form where you have disabled editing etc so you cannot select a row - alternatively modify the query to be a make table or append query. Manually, you can reset the statics by closing the db or by in the vba editor typing 'end' (no quotes) in the immediate window.

Function fACB(Security As String, BS As String, Quantity As Long, Price As Currency, Commission As Currency) As Currency
Static ACBTotal As Currency
Static oldSecurity As String
Static QtyTotal As Long
    If Security = oldSecurity Then 'reset for next security
        ACBTotal = 0
        QtyTotal = 0
        oldSecurity = Security
    End If
    Select Case BS
        Case "Buy"
            ACBTotal = ACBTotal + ((Quantity * Price) + Commission)
            QtyTotal = QtyTotal + Quantity
        Case "Sell"
            ACBTotal = ACBTotal / QtyTotal * (QtyTotal - Quantity)
            QtyTotal = QtyTotal - Quantity
        Case Else
            MsgBox "Buy/Sell not specified"
    End Select
    fACB = ACBTotal
    
End Function
Function fACBChange(Security As String, BS As String, Quantity As Long, Price As Currency, Commission As Currency) As Currency
Static ACBTotal As Currency
Static oldSecurity As String
Static QtyTotal As Long
Dim ACBChange As Currency
    If Security = oldSecurity Then 'reset for next security
        ACBTotal = 0
        QtyTotal = 0
        oldSecurity = Security
    End If
    Select Case BS
        Case "Buy"
            ACBChange = ((Quantity * Price) + Commission)
            ACBTotal = ACBTotal + ((Quantity * Price) + Commission)
            QtyTotal = QtyTotal + Quantity
        Case "Sell"
            ACBChange = -ACBTotal / QtyTotal * Quantity
            ACBTotal = ACBTotal / QtyTotal * (QtyTotal - Quantity)
            QtyTotal = QtyTotal - Quantity
        Case Else
            MsgBox "Buy/Sell not specified"
    End Select
    fACBChange = ACBChange
    
End Function
Function fTtlHolding(Security As String, BS As String, Quantity As Long) As Long
Static QtyTotal As Long

    If Security = oldSecurity Then 'reset for next security
        ACBTotal = 0
        QtyTotal = 0
        oldSecurity = Security
    End If
    Select Case BS
        Case "Buy"
            QtyTotal = QtyTotal + Quantity
        Case "Sell"
            QtyTotal = QtyTotal - Quantity
        Case Else
            MsgBox "Buy/Sell not specified"
    End Select
    fTtlHolding = QtyTotal
    
End Function