Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2015
    Posts
    4

    Unanswered: Stock Portfolio Database - Adjusted Cost Basis Calculation Help

    Hi,

    I am familiar with programming and very proficient with Excel, but this is the first time I have created a database. I am creating this for my own use to replace a spreadsheet that I have been using to track my investments. It's pretty clear to me that a database is a better solution, but I have having a very hard time understanding how to perform some of the calculations. Right now, I am trying to figure out how to calculate my adjusted cost basis (weighted average).

    I have four tables in my database:

    • Accounts (I have multiple stock accounts)
    • Dividends (for tracking dividend payments)
    • Equities (contains basic information about each equity I have traded)
    • Transactions (This is the main table)
    • Here is a picture of the relationships



    Equities Table looks like this:

    Code:
        TICKER ID	Description	   TICKER	Exchange	Current Price	Currency
        1	          Apple           AAPL	        NASDAQ     $114.34	USD
        2	          ATCO            ACO.X	        TSX            $35.60	CAD
        3	          Big Rock        BR	        TSX            $5.14	CAD
    Transactions table looks like this:

    Code:
        TRANSACTION ID	TICKER IDFK	ACCOUNT IDFK	Transaction Type	Date	Shares	Price	Commission	EXCH
        1	                   VAB	     TFSA	                Sell	08/04/2015	430	$26.50	$6.46	1
        2	                   VAB	     RRSP	                Sell	20/07/2015	390	$25.98	$6.32	1
        3	                   VAB	     RRSP	                Buy	10/08/2015	1	$26.09	$0.00	1
    Now, what I am trying to do is calculate my average cost basis with a query and produce a result like this (data totally made up):

    TICKER IDFK SHARES TOTAL COST TOTAL VALUE ACB UNREALIZED P/L REALIZED P/L
    AAPL 60 $6500 $6100 $108.33 -$400 $0
    BR 600 $3800 $4200 $6.33 $400 $0
    RSI 1500 $6200 $6250 $4.13 $50 $80

    And here is the basic idea. For RSI I could have transactions like

    1. Buy 500 @ $4
    2. Buy 500 @ $4.5
    3. Buy 500 @ $3.9
    4. Sell 300 @ $4.4
    5. Buy 300 @ $4.13



    The key is that selling shares does not affect the Adjusted Cost Basis as it results in a realized P/L that compensates. So, in the above example of transactions the ACB would be:

    1. $4 (500 * $4 / 500)
    2. $4.25 ((500*$4.5 + 500*$4)/(500+500))
    3. $4.13 ((500*$3.9 + 500*$4.5 + 500*$4)/(500+500+500))
    4. $4.13 ((-300*$4.13 + 500*$3.9 + 500*$4.5 + 500*$4)/(-300+500+500+500))
    5. $4.13 ((300*$4.13 + -300*$4.13 + 500*$3.9 + 500*$4.5 + 500*$4)/(300-300+500+500+500))



    And, I have no idea how to get a query to do these calculations.

    Thank you for your help.

  2. #2
    Join Date
    Dec 2015
    Posts
    4

    Database

    I reread the FAQ and saw that people like it when the DB is uploaded, and I can respect that, so please find attached the complete database as it currently stands. There is no specific query that I need help with as right now I have no idea where to start. All of the queries in the database are just examples of me playing around trying to understand queries and what data I will be able to get and various attempts and getting this data indicated above (not even close to being successful yet).

    StockPortfolio_DBForums.zip

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what you could do is use a subquery to find the sum of transactions for that specific stock and then use that sum in a JOIN to calculate the weighted value of each transaction.

    it still sounbds a very very odd way of calcuating value, I'd expect FIFO or LIFO taking into account actual values not trying to approximate soem meaningless theoretical value.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Dec 2015
    Posts
    4
    Quote Originally Posted by healdem View Post
    so what you could do is use a subquery to find the sum of transactions for that specific stock and then use that sum in a JOIN to calculate the weighted value of each transaction.
    I'll look into that, but I have no idea how to do a subquery or a JOIN at the moment. I've literally been looking at SQL for less than a week.


    it still sounbds a very very odd way of calcuating value, I'd expect FIFO or LIFO taking into account actual values not trying to approximate soem meaningless theoretical value.
    It's really not. The best way to understand your current unrealized P/L is to know what your average cost basis for the shares you own. That seems super intuitive to me. If I create a sample portfolio in Google Finance this is exactly how google finance calculates the adjusted cost basis. FIFO or LIFO make much less intuitive sense to me.

    More importantly, this is how the tax code works in Canada - adjusted cost basis.

    edit: So, I was wrong about Google Finance portfolio after running some tests on it. Google uses FIFO to calculate the cost basis. But, my accountant did tell me that a weighted average is the correct way of calculating value for tax purposes in Canada and it's the method that makes most intuitive sense to me.

    edit2: I've looked at JOIN a little now... I don't get how to do this calculation. Summing an individual stock for all buys and weighting those seems like a problem I can solve, but I don't understand how to handle selling.
    Last edited by sanadan; 12-22-15 at 13:10.

  5. #5
    Join Date
    Dec 2015
    Posts
    4
    I wanted to see if I could solve this using VBA within Access and it was pretty easy. Is VBA the only way to do it? A query would seem more natural.

    Here is the code that I came up with in a few minutes. The Query is set up to just select one stock (RSI) and then sort the transactions by ascending date.

    Code:
    Public Sub FirstAttemptAtCalculation()
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim rs As DAO.Recordset
        
        Set dbs = CurrentDb
        Set qdf = dbs.QueryDefs("qryRSICost")
        Set rs = qdf.OpenRecordset()
        
        Dim totalCost As Double
        Dim averageCost As Double
        Dim totalShares As Integer
        
        If rs.RecordCount <> 0 Then
            totalCost = 0
            averageCost = 0
            totalShares = 0
            
            rs.MoveFirst
            While Not rs.EOF
    
                If rs.Fields("TransactionType") = "buy" Then
                    totalCost = totalCost + rs.Fields("shares") * rs.Fields("price") + rs.Fields("commission")
                    totalShares = totalShares + rs.Fields("shares")
                ElseIf rs.Fields("transactiontype") = "sell" Then
                    totalCost = totalCost - rs.Fields("shares") * averageCost + rs.Fields("commission")
                    totalShares = totalShares - rs.Fields("shares")
                End If
                averageCost = totalCost / totalShares 'it gets updated for both buys and sells due to the commission
    
                rs.MoveNext
            Wend
        End If
        
        rs.Close
        Set rs = Nothing
        Set qdf = Nothing
        Set dbs = Nothing
    
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •