How to Convert Historical BNB Fees to USD using Excel Power Query
Question details
The user needs a reliable way to convert historical BNB cryptocurrency fees to USD by cross-referencing trade logs with a historical exchange-rate table.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Processing thousands of historical cryptocurrency trades and calculating exact fee costs in USD using a secondary table of exchange rates.
- Observed behavior
- Standard exact-match lookups fail because the timestamps in the trade logs and the exchange-rate tables differ by a few seconds, requiring an approximate match or timestamp normalization.
Ensure that both your trade history and your historical BNB/USD exchange-rate records are formatted as official Excel Tables before importing them into Power Query.
Normalize Timestamps and Merge Queries in Power Query
Use Power Query to round both sets of timestamps to the nearest minute, allowing for a successful table merge despite minor differences in seconds.
Because historical trade logs and exchange rate tables often originate from different systems, their timestamps frequently misalign by a few seconds. By importing both tables into Power Query and standardizing the time components, you can perfectly align the records.
Select any cell in your trade data table, go to the 'Data' tab, and click 'From Table/Range'. Repeat this process for your BNB/USD exchange-rate table.
In the Power Query Editor for both tables, select the timestamp column. Go to the 'Add Column' tab, click 'Custom Column', and use a rounding function or extract the date and time down to the minute to eliminate variance from seconds.
Navigate to the 'Home' tab and click 'Merge Queries'. Select your normalized timestamp column from the trade table and match it to the normalized timestamp column in the exchange-rate table.
Expand the merged exchange-rate column. Filter your 'Fee Currency' column to only include 'BNB'. Next, add a Custom Column that multiplies the original fee amount by the matched exchange rate to determine the exact USD value.
Click 'Close & Load' on the Home tab to output your new results into a green dynamic table. Whenever you add new trade data to the original blue tables, simply right-click the green result table and select 'Refresh'.

Try WPS Office for Your Data Processing Needs
If you do not have access to Microsoft Excel's Power Query, WPS Office provides a powerful, free, and lightweight spreadsheet alternative. You can easily manage thousands of rows of cryptocurrency data, perform approximate matches using XLOOKUP or INDEX/MATCH, and seamlessly calculate your financial data.
- 1. Download WPS Office: Visit the official WPS Office website to download the free installation package for your operating system.
- 2. Install the Suite: Run the downloaded setup file and follow the standard on-screen instructions to complete the lightweight installation.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheet and easily open your existing .xlsx workbooks to continue managing your cryptocurrency fee calculations.

Frequently Asked Questions
Why do exact matches fail when combining trade timestamps?
Exact matches often fail because timestamps inherently contain invisible seconds or milliseconds. If one system records a trade at 14:55:17 and another records the exchange rate at 14:55:31, an exact match lookup will fail. Normalizing the times to the nearest minute resolves this.
Can I calculate these fees without using Power Query?
Yes. You can use standard Excel or WPS Spreadsheet formulas like XLOOKUP or a combination of INDEX and MATCH configured for approximate matching (match mode -1 or 1) to find the nearest chronological exchange rate.
How do I update the calculations when I add new trades?
If you are using Power Query, simply right-click on the final output table and click 'Refresh'. The query will automatically ingest the new rows from your source tables, run the time normalization, and calculate the USD fees instantly.




