In trading data, it is common to see multiple purchases of the same cryptocurrency, often at different prices and quantities. If we want to compute the total quantity, total value, and weighted average price for each cryptocurrency, we need to organize and calculate the data. This article will guide you through achieving this using both Excel and Python.

Sample Data

Assume we have a trading dataset with the following columns:

• Time: The transaction time
• Cryptocurrency: The purchased cryptocurrency
• Transaction Direction: Buy (BUY) or Sell (SELL)
• Price: The individual price of the cryptocurrency
• Quantity: The amount purchased
• Total Value: The total cost of the cryptocurrency (Price × Quantity)

For example:

Alternatively, it might look like this: We need to calculate:

1. The total quantity of each cryptocurrency.
2. The total value of each cryptocurrency.
3. The weighted average price for each cryptocurrency, calculated as (Price * Quantity) / Total Quantity.

Method 1: Using Excel for Calculation

Excel’s pivot table feature is ideal for grouped statistics, though it lacks a built-in function for calculating weighted averages. Therefore, we need to adjust the process slightly:

Step 1: Create the Pivot Table

1. Open the Excel file and ensure the data is formatted correctly.
2. Select the entire data range (including headers), then click Insert -> Pivot Table.
3. In the popup window, choose New Worksheet, then click OK.

Step 2: Set Up Pivot Table Fields

1. Drag the Cryptocurrency field to the Rows area.
2. Drag the Quantity and Total Value fields to the Values area, making sure they are set to sum (which is the default behavior).

This setup allows you to obtain the total quantity and total value for each cryptocurrency.

Step 3: Calculate Weighted Average Price

1. Next to the pivot table, create a new column to calculate the weighted average price.
2. Assuming the Total Value is in column G and Quantity is in column F, enter the following formula in the first cell of the new column (let’s say H2):

   1

   =G2/F2

3. Drag this formula down to fill the cells below, calculating the weighted average price for each cryptocurrency.

Complete Pivot Table Example

The final table will look like this:

Method 2: Using Python to Calculate Weighted Average Price

If you prefer automating calculations with Python, you can utilize the pandas library for data processing. Below is a complete Python script to help you calculate the total quantity, total value, and weighted average price for each cryptocurrency.

Step 1: Install the pandas Library

If you haven’t installed the pandas library yet, you can install it using the following command:

1

pip install pandas

Step 2: Python Script

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

import pandas as pd

# Read the Excel file
df = pd.read_excel('trading_data.xlsx')

# Filter out the rows where the transaction direction is BUY
buy_df = df[df['Transaction Direction'] == 'BUY']

# Calculate the weighted price
buy_df['Weighted Price'] = buy_df['Price'] * buy_df['Quantity']

# Group by cryptocurrency and calculate total quantity, total value, and weighted average price
summary_df = buy_df.groupby('Cryptocurrency').agg(
    Total Quantity=('Quantity', 'sum'),
    Total Value=('Weighted Price', 'sum'),
).reset_index()

# Calculate weighted average price
summary_df['Average Price'] = summary_df['Total Value'] / summary_df['Total Quantity']

# Output the result to a new Excel file
summary_df.to_excel('cryptocurrency_summary.xlsx', index=False)

print(summary_df)

Explanation:

1. Read Data: Utilize pandas’ read_excel() function to load the Excel file.
2. Calculate Weighted Price: Use Price * Quantity to compute the weighted price for each transaction.
3. Group by Cryptocurrency: Group the data by cryptocurrency to sum Total Quantity and Total Value (sum of weighted prices).
4. Calculate Weighted Average Price: Finally, compute the weighted average price using Total Value / Total Quantity.
5. Output Results: Save the results into a new Excel file using to_excel().

Output Result

Summary

• Excel Method: You can quickly achieve cryptocurrency statistics through the pivot table and manual calculation of the weighted average price.
• Python Method: Using pandas allows for automated processing, making it particularly useful for larger datasets.

Choose the method that best fits your needs, and feel free to reach out if you have any questions or require further adjustments!