Search code examples
excelexcel-formulaaveragetrading

How to automate the calculation of the average entry price for every new entry price and contract size added in an Excel sheet? trading related

Context

  • Entry Price (a.k.a. Entry Point) refers to the price at which an investor initiates a position in a security. A trade entry can be initiated with either a buy order for a long position, or sell order for a short position.
  • Average Entry Price refers to the central price at which an investor is positioned in a security in order to increase potential profits and exit the market more easily, it can take different entry prices as well as different quantity of contracts (a.k.a. contract size)

Manual calculation of the Average Entry Price in an Excel Sheet

Based on this article, I managed to calculate the Average Entry Price as follows:

  • Say you have the following Entry Prices: [16500,16400,16300,16200]

  • And also you have the following Quantity of Contracts pegged to those Entry Prices respectively: [0.1,0.1,0.1,0.1]

Such information will be stored in a table like this one:

Entry Price Quantity (BTC) Average Entry Price
16500 0,1
16400 0,1
16300 0,1
16200 0,1

Now, the correspondingly average entry price formulas were written as follows (assume the position name of the columns is A,B,C respectively):

Entry Price Quantity (BTC) Average Entry Price
16500 0,1 =B3/(B3/A3)
16400 0,1 =(A3 * B3+A4 * B4)/(B3+B4)
16300 0,1 =(A3 * B3+A4 * B4+A5 * B5)/(B3+B4+B5)
16200 0,1 =(A3 * B3+A4 * B4+A5 * B5+ A6*B6)/(B3+B4+B5+B6)

Finally, the results were the following:

Entry Price Quantity (BTC) Average Entry Price
16500 0,1 16500
16400 0,1 16450
16300 0,1 16400
16200 0,1 16350

Problem

How could I automate the calculation of the Average Entry Price for every new value of Entry Prices and Quantity pasted in the last row of the table? The expected output would be something like this:

Entry Price Quantity (BTC) Average Entry Price
16500 0,1 16500
16400 0,1 16450
16300 0,1 16400
16200 0,1 16350
16100 0,1 16300
16000 0,1 16250
15900 0,1 16200
Solution

  • Assuming you are indeed working with a table as per your description:

    enter image description here

    Formula in C2:

    =SUMPRODUCT(INDEX([Entry Price],1):[@[Entry Price]],INDEX([Quantity (BTC)],1):[@[Quantity (BTC)]])/SUM(INDEX([Quantity (BTC)],1):[@[Quantity (BTC)]])

    This will now autopopulate upon new entries:

    enter image description here