Forum Discussion
How to quickly convert column of currency amounts to other currency using historical price data?
Thank you for the file and your support. In the attached file i've made a possible solution with Power Query. Can you check if this returns the expected result for the first 4 rows. For some reason many of the values from the Date(UTC) column of the left blue table aren't found in the Close time column of the right blue table. There seem to be rounding differences because of the seconds e.g. 03.05.2020 14:55 is shown in the cell but it could actually be 03.05.2020 14:55:17 in one table and 03.05.2020 14:55:31 in the other table. If the results of the first 4 rows are what you are looking for then i'll take a closer look at this. If i correctly understand you only want to calculate the fees in USD where column Fee from the left blue table contains "BNB".
That is correct. Only the cells in column G (left blue table) with BNB at the end of each number need converting to USD. Skip all the others.
Those 4 rows look good to me. Hopefully you can find a solution to the problem with values in the left-blue table not being found in right-blue.
I forgot to upload 2 more workbooks (1 table each) with additional rows of transactions that require BNB fee conversions. Both tables are in the same format as the SPOT table but with less rows of data (CROSS has 77, ISOLATED has 19). Sorry for not including these along with the SPOT file earlier. I promise this is everything.
Thank you again for your selfless efforts.