Take control of your international shopping finances. This guide shows you how to use formulas in a spreadsheet to automatically track your PinguBuy purchases, shipping fees, and refunds.
Why Automate Your PinguBuy Spending Tracker?
Manually calculating each month's spending on agent services like PinguBuy
Building Your Automated PinguBuy Spreadsheet
Create columns for the key data points from your PinguBuy account. A basic structure should include:
| Column | Description (Sample Data) |
|---|---|
| A: Date | 2024-11-05 |
| B: Item Name | Winter Jacket |
| C: Item Price (¥) | 350.00 |
| D: Domestic Shipping (¥) | 10.00 |
| E: International Shipping (¥) | 120.00 |
| F: Service Fee / Other (¥) | 5.00 |
| G: Refund (¥) | 0.00 (or -50.00 if issued) |
| H: Status | Shipped / Waiting / Refunded |
Essential Formulas for Automation
The real power comes from formulas. Here are the key calculations to implement:
1. Per-Order Total
In cell I2
Note: Refunds (column G) should be entered as negative numbers, so adding them effectively subtracts the amount.=SUM(C2:F2) + G2
2. Grand Monthly Totals
At the top or bottom of your sheet, create a summary section with these formulas:
Total Purchases:Total Shipping:Total Fees:Total Refunds:Net Spent This Month:
3. Dynamic Monthly Report
Use SUMIFS
=SUMIFS(I:I, A:A, ">="&K1, A:A, "<="&EOMONTH(K1,0))
Pro Tips for Maximum Efficiency
- Use Data Validation:
- Conditional Formatting:
- Currency Conversion:=J2*$L$1
- Pivot Tables:
- Conditional Formatting: