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.
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.
Hi Greg, thanks for this. It might be what I need to do. Can I ask a couple of questions:
do you manually add a 1 to column A to indicate the USD amounts? I guess this could be a formula.
other sheets currently read the “Amount” column to provide transaction analytics. Have you found a way to get them to read your new column instead, and without breaking the spreadsheet?
If your Tiller sheet is downloading transactions in Canadian currency as mine is, use the CADUSD closing value to convert transaction values to US dollars as in my earlier description.
If your Tiller sheet is downloading US dollar values for which you want CAD equivalents, reverse the order of the currency codes in the Column A formula from “CADUSD” to “USDCAD”. Alternatively, to get the reciprocal value in USD, divide 1 by the CADUSD value. For example, if the Canadian dollar closed at 0.77103 to the US dollar, divide 1 by 0.77103 to get the US dollar closing value of $1.29696. Adding 1 would give an erroneous value.
I have not revised any of the analytic sheets to use the converted transaction amounts. I suppose that one way to approach it would be to duplicate the analytical sheet in question, and redirect any applicable formulas from the Amount column to the Converted Amount column. I’d be interested to hear of your experience if you take that on.
I couldn’t repoint the formulas in the analytical sheets because most of them are populated by code rather than formulae.
What’s interesting is that analytic sheets seem to look for the first column named “Amount”, while the “Fill Sheets” process seems to populate the last column named “Amount”, so everything works as long as the converted column goes first.
I used your arrayformula but adapted it to show the “Amount” heading. I also amended it to use a REGEXMATCH formula (suggested in another thread) to only perform an FX conversion if the Tags column contains GBP. Now it reads: