# Unrealized Gain and Loss

To create pie charts like ones above, do the following:

** 1. ** Create or add a data range:

** a) ** with stock ticker symbols and prices (see
How to view
current stocks prices and other quotes in Excel).

For example:

** b) ** with transactions (if you have a fixed quantity, you can add
them without an additional calculation).

For example:

** 2. ** Add new columns to calculate current value, Gain and Loss:

**Quantity**(if you have fixed quantity, you can add them without additional calculation) using the following formula:*= SUMIFS (Transactions[Quantity], Transactions[Symbol], "="&<symbol>)*Where:

**SUMIFS**is a function to sum cells that meet multiple criteria:*SUMIFS (<the range to be summed>, <the first range to evaluate>, <the criteria for the first range>, [<the second range>], [<the criteria for the second range>], ...)**Transactions*is a table from p.1.b),*<symbol>*is a stock symbol from column.*C*

For the cell

this formula is:*E4**= SUMIFS (Transactions[Quantity], Transactions[Data], "="&C3)*:**Value**using the formula:*= <price> * <quantity>*For the cell

this formula is:*F4**= D4 * E4*:: You can hide the price and quantity data and use the formula:*Note**= <stock>.Price * SUMIFS (Transactions[Quantity], Transactions[Symbol], "="&<symbol>)*Where,

*<stock>.Price*is a reference to the**Price**field of the**Stocks**data type.For example:

**Gain**:*= IF ((<value> - <cost>) > 0, <value> - <cost>, 0)*Where:

*<value>*is a calculated amount from the previous step,*<cost>*can be calculated like a*<quantity>*using the formula:*= SUMIFS (Transactions[Amount], Transactions[Symbol], "="&<symbol>)*

For this example:

**Loss**:*= IF ((<cost> - <value>) > 0, <cost> - <value>, 0)*For this example:

** 3. ** Select the data range to create a pie chart.

For this example, ** C3:C7** and

*.*

**E3:E7**
** 4. ** On the **Insert** tab, in the **Charts** group,
click the **Insert Pie or Doughnut Chart** button:

Choose the **Pie** chart

Make any other adjustments to get the look you need.