How to create a formula to convert a cryptocurrency denominated in USD to BTC?

Hello SG!

Yes that formula will give the correct number, it is actually the same formula I mentioned in my orginal post. The problem occurs when I sort the table or when I add rows. Near the middle and near the end of my post I describe how the formula gives me problems if I sort the table or add rows, and also how I tried various syntax methods to make sure the formula pointed to the correct reference cell but I kept getting an error (as per my 2nd last paragraph in my original post). So as you mentioned in your reply, knowing what to divide is working fine, but getting it to work in a table that I sort and add rows to is giving me some troubles. If you have any insight I'd love to hear back. Thanks again. - Rod

Hello again SG!

Unfortunately in my case switching to horizontal instead of vertical would not be practical as it's possible that the quantity of different cryptos could easily be in the hundreds and I think I'd just end up with the same problem if I sorted or added columns.

Are you aware of a method to either:

A] tell the formula to "find" BTC-USD as opposed to referencing a cell

or

B] create syntax that will divide whatever coin is in column A by the yahoo price import of BTC-USD

Method B as I mention in this post is the one that makes the most sense to me, however I can't figure out what syntax to use. I am close but somehow the syntax is off by a little bit. If you see my original post and look at the second last paragraph that starts with "I tried substituting..." you'll see that the syntax I'm using does come close to being able to divide a coin in a column by the Yahoo price import of BTC-USD but instead of finding the coin symbol and bringing in the price it did it the other way around. Somehow my formula thinks that the price imported is the symbol. So I think Apple Numbers can perform the calculation I'm looking for but I cant' figure out the right syntax.

Hello SG,

Let's see if I can clarify: I'm trying to find a way to convert USD price of a crypto into the BTC price so I can track my gain / loss in BTC. The reason for that is some cryptos are paired with BTC and the gain or loss is in BTC, not in USD.

The Yahoo price import does support some coins paired with BTC and the price import in Apple Numbers for them is fine, but for reasons unknown Yahoo is missing a lot of coins that are paired with Bitcoin (BTC) so the only way to get a price import into Apple Numbers is to import the USD price and then use a formula to convert it to the BTC equivalent.

FYI on a related note, I have messaged Yahoo to request those coins to be supported but the only reply I can get is "we will forward your request to be considered" despite the fact that they have both a user forum and a dedicated form to have them added. A quick look at the Yahoo forum will quickly reveal that requesting they add a pair is pretty much a waste of time based on the number of exasperated users who have been trying to get commonly used cryptos added to the price file.

Back to my scenario: As an example RSR is supported but AKRO is not, so from the Apple Numbers STOCK function:

RSR-USD price is: 0.01313

RSR-BTC price is: 0.00000033234312

AKRO-USD price is: 0.011

AKRO-BTC price is: not available. The only way to get the AKRO-BTC price is to look it up on a crypto exchange, crypto coin tracker, or to simply divided ARKO-USD by BTC-USD. The price actually is 0.00000028 (which is simply the 0.011 divided by the price of BTC).

So calculating the price is simple division which I can get Numbers to do.

But here is where it starts to get problematic:

I have separate trade tracking tables where I have inputted my buy / hold / sell transactions, and those tables import the price of cryptos based on the pair, so for example I may input a buy price of 0.00000027 as my purchase price of AKRO-BTC so now I need a method to import the current price of AKRO as denominated in BTC.

I have a 'Price Imports' table with hundreds of cryptos. The trade tracking table pulls in the prices from this Price Imports table. If I add a row for AKRO-BTC and input a formula to divide the AKRO-USD price by the BTC-USD price it will give me the number I need.

But... if I sort the table or if I add more cryptos, the formula gives me the wrong price. For some reason the calculation to divide AKRO-USD (numerator) by BTC-USD (denominator) will use the wrong denominator if the table is sorted or if rows are added.

Solution attempt # 1 is to find a way to tell my trade tracking table to always find and use BTC-USD but I can't figure out the syntax to get it to do that. It seems to me that there has to be a way to tell the formula to point to the correct cell; it seems odd to me that just sorting the table or adding rows is a problem that can't be overcome.

Solution attempt # 2 is to figure out the syntax that will import the price of AKRO-USD and then import the price of BTC-USD and divide one by the other, but the way I have the syntax scripted currently for some reason it is not looking up BTC-USD but instead it thinks the current price ie $40,000 is what I'm trying to find so it says that $40,000 is not a valid symbol. It seems to me that the formula is close to correct but for some reason the syntax is not quite right.

I would think that there just has to be a way to resolve via method 1 or 2.

However I did just think of a hack that could work. I realized I could put BTC-USD in a header or footer row and point the formula there; any table sorting or adding rows for more cryptos would then not affect the formula. However I am not sure that will work because then my XLOOKUP formula in the trade tracking may not work; I don't know if it would look in header or footer rows so that idea might fail. Regardless I have not tried that yet as it seems like more a hack than a genuine fix.

Hopefully this clarifies what I'm trying to achieve, let me know if you have any insight,

Thanks again,

Rod

Thanks to both of you, SG & Barry, I've implemented the solution in my spreadsheet and it's perfect!