Quickly convert column of currency amounts to other currency via historical data?

Welcome to the Forum b-Water!

You can do it with the files you have, if the row limit does not cut off before the dates you need. I would be delighted to test this on your files if you attach them but in the meantime try this:

Formula:

=VLOOKUP(G2,'[BNBUSD_1m_Binance]BNBUSD_1m_Binance'!$A:E$,5,FALSE)

I am using the closing price here.

If it does not find an exact match for the date/time it uses the latest quote before that date/time.

If this does not work for you please attach sample files. You will probably have to zip them, maybe separately. If the zip file is still >9.77MB (if your file is 400MB then it probably will be) then we'll have to figure something else out. See yellow banner at the top of the page.

You can upload the Excel file here. No need for a link.

The file is 434MB. It's over 100MB even when zipped. I was able to download it using the link in the .txt file.

I edited down your large file to start with your earliest needed dates and it's still too big to fit in an Excel file.

I didn't notice before but now I see that column A of your data file is in UTC format d/m/yyyy. This is very messy (at least for me) because if Excel can convert it to a date it will, so some of your data is dates and some is text. And the conversion to a U.S. date may not be correct.

This shows what I'm seeing:

dates.jpg

I think this will not be as simple as I first expected. I'm giving a heads up to Alan, who is our resident Power Query expert.

I think you can do this with Power Query. In the Spot file, Convert the UTC Date Time to US with the Date separated from the Time. ie. Split the columns. Here is the Mcode to do that.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedTypeToText = Table.TransformColumnTypes(Source,{{"Date(UTC)", type text}}),
    SplitColumnbyDelimiter = Table.SplitColumn(ChangedTypeToText, "Date(UTC)", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Date(UTC).1", "Date(UTC).2"}),
    ChangedTimeColumnFormat = Table.TransformColumnTypes(SplitColumnbyDelimiter,{{"Date(UTC).2", type time}}),
    SplitColumnbyDelimiter1 = Table.SplitColumn(ChangedTimeColumnFormat, "Date(UTC).1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Date(UTC).1.1", "Date(UTC).1.2", "Date(UTC).1.3"}),
    MergedColumns = Table.CombineColumns(SplitColumnbyDelimiter1,{"Date(UTC).1.2", "Date(UTC).1.1", "Date(UTC).1.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"M/D/Y"),
    ChangedTypeToDate = Table.TransformColumnTypes(MergedColumns,{{"M/D/Y", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedTypeToDate,{{"Date(UTC).2", "Time"}}),
    SortedRows = Table.Sort(RenamedColumns,{{"M/D/Y", Order.Ascending}})
in
    SortedRows

In the BNBUSD file, split the time from the Date and then delete all columns except closing price column and time and date. I assumed you wanted the closing price for that time and date. If not, change the columns to delete so match your needs.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(Source, {{"Open time", type text}}, "en-US"), "Open time", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Open time.1", "Open time.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Open time.1", type date}, {"Open time.2", type time}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Open time.1", "Open time.2", "Close"})
in
    #"Removed Other Columns"

Close and Load each query.

In a new file, load each of the queries into the PQ editor and do a full outer join of the two queries (in PQ speak, this is a merge). Highlight the dates and times in each query as the common field to join on. The end result should give you the exchange rate for that time and date. This last merge, I did not test as you did not provide exchange rates for 2020 in the file I was able to download.

I hope this is what you are looking for.

As to the error message when Alan's code is applied to the SPOT file, I imagine that the issue is the range A1:G307 on the SPOT sheet was not converted into an Excel table.
In the attached file the data has been converted into an Excel table and Alan's code (top of post) was applied to produce the table on the Table 1 sheet.

@JeteMc
Thanks for updating the OP on this issue.
Alan

@Alan, You're Welcome.
I hope that you have a blessed day.

The file that you presented to us does not have any 2020 data in it. I brought the file into Power Query and Power Pivot. Both files show only 2017 data. There is no limit on the number of rows in either of those programs and they both only show 1,048,575 rows of information. Can you provide another link to the file where it shows the 2020 data. You need to load it to PQ from a blank worksheet.

Data-->Get and Transform Data-->From Text/CSV

This should load the file into Power Query. You can then split the DateTime and filter out the non necessary columns and rows.

The 2020 data actually is there. By some miracle, I found it.

I can’t remember how I did it but playing around in PQ and deleting millions of rows got me all the minute-by-minute exchange rates for 2020 I’m looking for.

I think before deleting rows, the row count that came up was over 3,000,000 for me so I’m not sure why you only got 1,048,575.

I'm stuck now with the same problem as before trying to use your Mcode. I get the exact same expression error that Jete was able to fix for me.

A link to download the now 6.7mb bnbusd data file is attached if you want to look at it. With any luck, maybe Jete will come back and perform his fix again for it like the spot file.

It appears that the screenshot is of an Excel table that includes the minute-by-minute exchange rates for 2020.
Please attach a copy of the workbook from which that screenshot is taken to your next post.

To clarify, if only the minute-by-minute exchange rates for 2020 are put into a file that file would be 6.7 mb. Is that correct?
I went to the link in the .txt file but instead of seeing a file, I only saw a page that wanted me to sign up for drop-box.

Here is the page that link takes me to: https://limewire.com/
If you could attach just the 21 rows of the sheet that are shown in the screenshot in post #15 then we could get the PQ code working.
Then, assuming the actual table has the same name and column headings, the code should be able to be applied.

I imagine that you get that screen because you already have a LimeWire account.
I imagine that the reason other members were able to open the Dropbox was that they have an account.
Alan's code assumed a table named Table 1 whereas the actual table name is BNBUSD_1m_Binance___Copy. Also it appears that instead of using the Open time column the Close time is being used.
Modified code:

let
    Source = Excel.CurrentWorkbook(){[Name="BNBUSD_1m_Binance___Copy"]}[Content],
     #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(Source, {{"Close time", type text}}, "en-US"), "Close time", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Close time.1", "Close time.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Close time.1", type date}, {"Close time.2", type time}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Close time.1", "Close time.2", "Close"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Close", type number}})
in
    #"Changed Type1"

Let us know if you have any questions.

I managed to navigate the LimeWire site and open the file.
The code used in post #22 worked on the 347K+ row file.
Perhaps on your computer the file is named "BNBUSD_1m_Binance" rather than "BNBUSD_1m_Binance___Copy", other than that I don't know why the code would not have worked.
I have uploaded the new file.
Here is a link to LimeWire: https://limewire.com/d/d9330acc-f5f3...YXFddlUMvT0zTI
Let us know if you have any questions.

I could not get the merge working, however here is a formula based proposal that may do what you want.
The formula in column H of the Table 1 sheet is: =INDEX(SPOT_1[Fee],AGGREGATE(15,6,(ROW(SPOT_1[Fee])-ROW(SPOT_1[#Headers]))/(SPOT_1[M/D/Y]=[@[Close Day]])/(SPOT_1[Time]=[@[Close Time]]),1))
https://limewire.com/d/8db2c9b0-732b...pyYudt0NK8syk0
Let us know if you have any questions.

The #NUM! errors occur at date and time slots where there are no matching date and times.
For example, the first date and time on Sheet 1 is 4/11/2020 at 6:56:00 AM.
On the Table 1 sheet that date and time is in row 455.
What should go in the preceding 454 rows?

Add a column called Fee2 to the table on Sheet1 and populate it using:

Formula:

=IF(RIGHT([@Fee],3)="BNB",LEFT([@Fee],LEN([@Fee])-3)+0,"")

Change the formula for column H on the Table1 sheet to read:

Formula:

=SUMIFS(SPOT_1[Fee2],SPOT_1[M/D/Y],[@[Close Day]],SPOT_1[Time],[@[Close Time]])

Let us know if you have any questions.

I now have all BNB fees converted.

I have arranged the sheet with all rows grouped into their respective trades.

Can I quickly calculate the P/L on all trades instantly via formula?

If all trades had only 2 rows each (1 buy and 1 sell) then I could do it myself but I get confused on how to make it work when there are trades with e.g. 4 rows (1 buy and 3 sells).

Every trade is a long position so PL calculation would = subtract position value of buy (F3) then subtract total fees (G3:G4) then add position value of sell (F4).

https://limewire.com/d/4f424920-5e4c...YfsM7_FF32hIWs

As there are no manually calculated results to use as a guide, this is a guess.
Populate cells H3 and down using: =IF(C3="SELL",D3-INDEX(D2:D$3,AGGREGATE(14,6,(ROW(D2:D$3)-ROW(D$2))/(C2:C$3="BUY"),1)),"")
Let us know if you have any questions.

The easy way to remove the "USDT" text is to produce another column (H) populated using: =IF(F3="","",--LEFT(F3,LEN(F3)-4))
I put the following into cell I12: =-SUM(G9:G12)-SUM(H9:H11)+H12
You didn't tell us whether the cell that should be populated was in row 31 or 32. I guessed 31.
Once it is determined what the correct cell is then we can attempt to write a formula to fill cells I3:I390 with a single formula.
As the file is now small please attach it directly to your posts instead of linking Limewire.
Let us know if you have any questions.

Here is a formula that seems to work for P/L:

Formula:

=IF(AND(C2<>"",C3=""),-SUM(INDEX(G$2:G2,AGGREGATE(14,6,(ROW(G$2:G2)-ROW(G$1))/(ISBLANK(C$2:C2)),1)):G2)-SUMIFS(INDEX(H$2:H2,AGGREGATE(14,6,(ROW(H$2:H2)-ROW(H$1))/(ISBLANK(C$2:C2)),1)):H2,INDEX(C$2:C2,AGGREGATE(14,6,(ROW(C$2:C2)-ROW(C$1))/(ISBLANK(C$2:C2)),1)):C2,"BUY")+SUMIFS(INDEX(H$2:H2,AGGREGATE(14,6,(ROW(H$2:H2)-ROW(H$1))/(ISBLANK(C$2:C2)),1)):H2,INDEX(C$2:C2,AGGREGATE(14,6,(ROW(C$2:C2)-ROW(C$1))/(ISBLANK(C$2:C2)),1)):C2,"SELL"),"")

Let us know if you have any questions.

Paste the following formula into cell I3 and then double click on the fill handle to copy down to cell I390:

Formula:

=IF(AND(C3<>"",C4=""),SUM(INDEX(H$2:H3,AGGREGATE(14,6,(ROW(H$2:H3)-ROW(H$1))/(ISBLANK(C$2:C3)),1)):H3),"")

Let us know if you have any questions.