Calculate Average Cost of a Stock Position

Asked

Viewed 308 times

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

  • 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.

  • 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!

  • 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

1 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.

  • Hello Cafi. Aiming to unite the idea of the accumulated average plus my idea of covering a whole portfolio, I ask: This example is only a part of the project? Or does Voce always use this way, inserting the asset for research? I’m trying to adapt the accumulated media model to assemble the whole tb and arrive at a satisfactory result. Even if you are using another tool I encourage you not to give up. I will join forces.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.