0
Hello, I’m trying to create a query that calculates for all dates the average cost of the action. The formula is described below.
The Average Cost Price formula in T0 is: ((Accumulated Position T-1 * Av Cost Px T-1) + Qtty T0* Price T0)/ (Accumulated Position T-1+ Qty T0)
If the record is for sale, I just subtract the amount from the increased position and keep the average cost
To lost on how to do this, subquery does not work for the same query, and do not know how to get the value of a record of the same query. Qqr light will be welcome!
Example
In the table below I have trading the asset Yum. On the first day, I bought 9 thousand quantities at the price of 88.97. So the average cost is 88.97.
On the second day, I bought 3,000 quantities at 89.81. I now have 12,000 quantities at an average price of 89.18. The calculation was:
((price Day 0 * Quantity Day 0) + ((Av Cost Price Day -1)*(Cumulative Quantity Day -1)) / (Qty Cumulative Day 0)
Every day I have purchase, I make this calculation. If I have a sale, I simply use the average price of the last purchase.
To calculate the accumulated amount I used a subquery.
╔═══════════╦════════╦══════════╦══════════╦════════════════╦═══════╦═══════════════╦═════════════╗
║ Date ║ Ticker ║ buy_sell ║ Qtdade ║ SumOfNotional ║ Preço ║ Qtd Acumulada ║ Preço Médio ║
╠═══════════╬════════╬══════════╬══════════╬════════════════╬═══════╬═══════════════╬═════════════╣
║ 28-May-20 ║ YUM ║ B ║ 9,000 ║ 800,760.00 ║ 88.97 ║ 9,000 ║ 88.97 ║
║ 29-May-20 ║ YUM ║ B ║ 3,000 ║ 269,430.00 ║ 89.81 ║ 12,000 ║ 89.18 ║
║ 01-Jun-20 ║ YUM ║ B ║ 6,000 ║ 545,095.50 ║ 90.85 ║ 18,000 ║ 89.74 ║
║ 04-Jun-20 ║ YUM ║ B ║ 3,000 ║ 282,318.76 ║ 94.11 ║ 21,000 ║ 90.36 ║
║ 08-Jun-20 ║ YUM ║ S ║ (3,000) ║ (287,970.00) ║ 95.99 ║ 18,000 ║ 90.36 ║
║ 10-Jun-20 ║ YUM ║ S ║ (3,000) ║ (284,558.70) ║ 94.85 ║ 15,000 ║ 90.36 ║
║ 16-Jun-20 ║ YUM ║ S ║ (7,000) ║ (654,720.91) ║ 93.53 ║ 8,000 ║ 90.36 ║
║ 18-Jun-20 ║ YUM ║ B ║ 2,000 ║ 179,560.00 ║ 89.78 ║ 10,000 ║ 90.25 ║
║ 19-Jun-20 ║ YUM ║ B ║ 7,000 ║ 622,670.00 ║ 88.95 ║ 12,000 ║ 89.54 ║
║ 19-Jun-20 ║ YUM ║ S ║ (5,000) ║ (457,820.00) ║ 91.56 ║ 12,000 ║ 89.54 ║
║ 22-Jun-20 ║ YUM ║ B ║ 12,000 ║ 1,055,360.00 ║ 87.95 ║ 24,000 ║ 88.74 ║
║ 24-Jun-20 ║ YUM ║ B ║ 21,000 ║ 1,821,280.00 ║ 86.73 ║ 45,000 ║ 87.80 ║
║ 26-Jun-20 ║ YUM ║ S ║ (45,000) ║ (3,808,740.00) ║ 84.64 ║ - ║ 87.80 ║
╚═══════════╩════════╩══════════╩══════════╩════════════════╩═══════╩═══════════════╩═════════════╝
SELECT db_TradeHistory.Date, db_TradeHistory.Ticker, db_TradeHistory.buy_sell, Sum(db_TradeHistory.Quantity) AS SumOfQuantity, Sum(db_TradeHistory.Notional) AS SumOfNotional, [SumOfNotional]/[SumOfQuantity] AS Preço, (SELECT Sum(Dupe.[Quantity]) AS SumOfQuantity
FROM db_TradeHistory AS Dupe
WHERE (((Dupe.Date)<=[db_TradeHistory].[Date]) AND ((Dupe.Ticker)=[db_TradeHistory].[Ticker]))
ORDER BY Sum(Dupe.[Quantity]) DESC) AS [Qtd Acumulada]
FROM db_TradeHistory
GROUP BY db_TradeHistory.Date, db_TradeHistory.Ticker, db_TradeHistory.buy_sell
HAVING (((db_TradeHistory.Ticker)="YUM"));
i am not an expert in SQL and I use Access by interace and because I use only to contole these positions, but I am caught in this problem, I do not know if it is possible to do this with SQL or if I have to try another solution.
This answers your question? Display Average in an SQL query
– Rafael
T0 is the starting position (time 0) ? T-1 the previous position ? I find it difficult to do this in ms-acess sql. Edit the question to identar a sql and publish the model , a numerical example would help.
– Motta
Hi Motta, this. T0 is the position of the day, and T-1 the day before. thanks for the tips, I added more information and the numerical example of projeot. Please let me know if it’s enough. Abs!
– Cafi rf
I think that ms-acess hinders solutions of type cumulative totals (running Totals) and previous values (lead & lag), the rest would apply this variant of the weighted average. Maybe it’s easier in Excel. https://www.techrepublic.com/article/how-do-i-generate-a-running-total-in-an-access-query/ https://techcommunity.microsoft.com/t5/access/available-lead-and-lag-function-in-ms-access/m-p/1401504
– Motta