Calculate Average Cost of a Stock Position

Question

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.

Answer 1

I think with my problem I can help with something: I have a similar problem, but I built it differently because I couldn’t find specific matter. After having approved the data inclusion table I followed with virtual Tables in SLQLITE until I found a problem that I’ve been looking at for a long time. First of all it should be taken into account that everything that refers to shares finally investment assets is applied weighted average and not simple average, then it bumps into the problem that if we have a buy and sell table in one we have to limit the stop to averages to each lot sold and reset, so there should be again a reset and start all over again if it is

repurchase the same asset for example. Here are some excerpts that I worked out until I got to this:

source table:

  id       INTEGER PRIMARY KEY AUTOINCREMENT,
  idNC            INTEGER, 
  data_pregao     DATE, '+
  num_nc          VARCHAR(12), 
  C_V             VARCHAR(1), 
  tipo            VARCHAR(12), 
  ticker          VARCHAR(14), 
  qde             FLOAT, 
  saldo_qde       FLOAT, 
  tot_item        FLOAT, 
  tot_custos      FLOAT , 
  tot_opers_liq   FLOAT,         //calculado
  custo_item      FLOAT,         //calculado
  val_item_bru    FLOAT,         //calculado
  val_un_medio    FLOAT,         //vazio
  valor_total     FLOAT,         //vazio
  arquivado       DATE;

Determine and Post unit weighted average value calculation:

UPDATE tbtempor AS A SET val_un_medio = (SELECT 
        (SUM(round(B.val_item_bru,2)) / SUM(round(B.qde,2))) 
        FROM tbtempor AS B '+
        WHERE   B.C_V = "C" AND B.cod_ativo = A.cod_ativo  
        GROUP BY cod_ativo);


          
         

With this source table in hand and with the auxiliary calculations ready I go to the teporária tab for the current portfolio:

CREATE  TABLE tbcarteira  AS 
SELECT A.id,
A.data,    
A.C_V, 
A.tipo,
A.ticker,
SUM(A.qde) - '+         //ABATIMENTOS AQUI
                COALESCE((SELECT SUM(B.qde) 
                FROM tbtempor AS B '+
                WHERE B.ticker = A.ticker   AND   B.C_V = "V"  
                AND DATE(arquivado) IS  NULL),0)  AS qde, 
A.val_item_bru, 
A.val_un_medio, 
A.valor_total, 
A.arquivado 
FROM tbtempor AS A 
WHERE A.C_V = "C" AND DATE(A.arquivado) IS  NULL 
GROUP BY ticker ;

Note I haven’t normalized Ticker yet until I find a solution.

I put a Trigger to when reset some sale mark where. But I could not proceed because he sees the whole table by GROUP BY and I can’t book a sale.

Finally, another module in which I demonstrate the negotiations and details of them. So far so good but how to delimit when a lot sold to start another??

I should give an answer instead of a question but perhaps this model to take some advantage. I think even to migrate to this form by accumulation of results but I do not know if it is worth the effort.