I am struggling a bit with a simple task I suppose. I could not go further. I will explain below what I need and where I got stuck. I created a packaged worbook on Tableau 10.4 with no real values or names, so we may test with it. I also create a excel file with the calculation working, however I could not reproduce it on Tableu due to a circular reference.
The scenario is the following:
I have an excel file with stock market operations, showing the quantity bought or sold and the unit price; per person.
Image 1: Shows that Person 1, bought 2000 stocks of ABC on 14/07 by $7,32 and 3400 stocks on 14/07 by $7,26. On 20/07, bought more 3600 stocks by $7,12 and on 26/07 sold all of the 9000 previous stocks, zeroing his portfolio, no more stocks in the stock market to operate. On 28/07 the person bought 5000 new stocks by $7,69
I will show below a different way to view the same scenario:
Image2: I created a calculated field Qty buy/sell just making negative when the order="SELL". Then I created a running sum of this calculated field Qty buy/sell. You can see that on day 26/07, 02/08 and 25/08 the person sold all his stocks.
What I need?
I need just to calculate the average of unit price of the stocks the person have in his portfolio (highlighted in yellow). That means, the average unit price of the 700 stocks + 9500 stocks, unit prices 8,87 and 8,88. Because the previous stocks have already been sold, as the Running SUM shows us "0" in red.
Person 1 scenario above may seem like a simple task. I will show below the scenario of Person 3 portfolio and the problem I got stuck with:
Image 3: You can see that on date 02/08 the person sold all his portfolio. I have to calculate the average of unit price after 09/08, highlighted in yellow. Note that the person sold stocks on 24/08, 70.000 and 100.000 stocks, but did not zeroed his portfolio.
Looking at the quantities bought and sold and the Running SUM, I have to calculate the unit price for below orders:
+ 105.000 stocks on 09/08 (Running sum= 105.000)
+ 125.000 stocks on 18/08 (Running sum= 230.000)
- 70.000 stocks on 24/08 (Running sum= 160.000)
- 100.000 stocks on 24/08 (Running sum= 60.000)
+ 8.800 stocks on 28/08 (Running sum= 68.000)
+ 58.400 stocks on 29/08 (Running sum= 127.200)
Now, what I did to calculate the average of the unit prices of the portfolio:
Image 4: I create four new columns on Excel to calculate the average of the portfolio.
- 1st column "Qty x Unit price" = For "BUY" orders we use "Qty buy/sell" * Unit Price
- 2nd column "Running SUM of Qty x Unit" = Calculated field with running sum of the first column "Qty x Unit price"
- 3rd column "Running SUM of Qty" = Calculated field with running sum of the "Qty buy/sell" calculated field
- 4th column "Average Unit Price" = This calculated field is the tricky one. For "BUY" order of 12/07, the average is calculated normally: "Running sum of QtyxUnit" / "Running sum of Qty" ($891.750 / 135.000 = 6,606).
However for "SELL" order of 13/07, we have to set the average price of the last "BUY" order average, the last BUY average is: 6,606. (I used lookup(Average unit price, -1) )
After that, returning to 1st column, for "SELL" orders, we have to calculate differently from "BUY" orders. For SELL order 13/07, instead of using the unit price of 7,1. We have to use the last "BUY" average. The total value of the "SELL" order is: Quantity 135.000 on 13/07 * the last BUY average price 6,606 (135.000 * 6,606 = $891.750).
So for the SELL order, the Running SUM of QtyxUnit is 0. So the portfolio was zeroed. Then on day 21/08 29.000 stocks were bought, then the calculation continues as the same as before.
On Excel was possible, on tableu occurs Circular Reference error. I can see why is occuring the circular referente, but I cannot think how can I solve this problem in another way. The main thing is that I have to use the last average of the "BUY" order on the "SELL" order to know what is my average price of BUY of my portfolio
Below is the image of the tableau calculated field. I tried creating another calculated field just to get the last "BUY" average. However the calculated field "Qty x unit price" occurs Circular Reference error still.
I just need to solve the circular reference, I thought it would be easy, but I can think how I can fix it.
After that I have to present a table just with the average of the unit price for each person as below:
Image 5: Balance quantity is the Running sum of Qty, the portfolio of each person (BUY - SELL)
Thank you all in advance