Is there a way to convert a manual account's currency?

Hello, I’m relatively new to Tiller and spreadsheets in general, and I apologize if this has been asked before.

I have a manual account in which I track my cryptocurrency purchases, however, the account’s balance does not reflect its USD value. I intend on multiplying the account’s balance with the following formula:

GOOGLEFINANCE(“CURRENCY:BTCUSD”)

However, I’ve yet to find a way to do this without breaking the spreadsheet in some way. Are there any suggestions? Thank you for your time!

Edit: For clarification, what I am looking for is my ‘Balance’ sheet and ‘Net Worth’ sheet to reflect Bitcoin’s USD value, all while not disrupting the normal function of all the spreadsheets. I’m looking for a way to inject the googlefinance formula in order to automate this process.

Hi, Tyler:

I have Canadian accounts which I track in their own Tiller sheet. To this I’ve added a sheet named Exchange Rate History. In this sheet (Column A) I use the formula: =GoogleFinance(“cadusd”,“price”,date(2016,7,1),today())
This give me the closing CAD/USD rates from 7/1/2016 through today.

In my Transactions sheet I’ve added a column with the Array formula:
=ArrayFormula( IF( ROW( $A:$A ) = 1 , “USD” , IFERROR (IF((ABS(E:E)(VLOOKUP(B:B,‘Exchange rate history’!A:B,2,TRUE)))>0,(E:E(VLOOKUP(B:B,‘Exchange rate history’!A:B,2,TRUE))),))) )
This generates the label “USD” and looks up the transaction date of each row, and applies that day’s rate to the native currency amount, converting it to US dollars.

This gives me a timely converted value for each transaction that can be used in additional calculations without losing the original exchange rate.

This is only accurate up to a point. Any deposit I make to these Canadian accounts locks in the US rate on the day of that deposit, so the US cost to me of those Canadian dollars doesn’t change no matter when I spend them. It also does not reflect any fees or expenses incurred when the currency was exchanged. However, it gives me a pretty accurate read on the comparable cost of any individual transaction.

I hope this is of some use to you.

2 Likes