I want to be able to track cash transactions in a different currency while traveling while keeping the most accurate conversion rate.
I’m wondering if there is a Tiller community add-on that can add a cash transaction where I can specify the currency, date of transaction and have that transaction be automatically added to my Transactions tab.
We don’t have this yet, @asdf, but the workflow is on our radar. For now, I’d recommend adding new columns to the Transactions sheet like Base Amount and Conversion Rate then using a formula to feed that value into the Amount column which is wired to most Tiller templates.
@asdf, I have occasionally thought about doing this, and your query has made me curious: How are you capturing the details of your expenditures while traveling, and when has the actual currency exchange taken place?
I’ve considered the options in a few common situations, such as when using a credit or debit card, where I may note the purchase amount in foreign currency, and link this back to a line item on my statement, or exchanging a sum of USD for foreign currency, and tallying any expenses paid from the proceeds.
I do something with cash expenses in USD that might work here: If I get a sum of cash from the ATM, the transaction appears in Tiller, as expected. Later, when I spend some of that cash and want to document that transaction in Tiller, I split the expense from the original ATM transaction and edit the date and Description accordingly. You’d have to document the exchange rate for each these splits in the notes field or an added column, I assume.
It should be noted that by using this method the Tiller register will not now match my bank’s register, since the ATM transaction value has been reduced by any splits drawn from it, though this does not concern me. Any split from the ATM transaction will append the date and amount to the Notes field, creating a rudimentary audit trail. I edit this when I feel the need.
In a related situation, I am in Canada often enough to warrant having a Canadian bank account, for which I have a separate Tiller sheet. To that sheet I have added a column to the Transactions tab that calculates the USD value for each transaction on the day it occurred, via lookup to an added tab containing the closing current exchange rate as derived from a Google Finance function. This approach is more dynamic, but requires more setup and resources, and only works with one additional currency.
I’d be interested to hear more about your objectives in this case.
How are you capturing the details of your expenditures while traveling, and when has the actual currency exchange taken place?
Firstly I try to pay for everything with my US credit card. A lot of businesses don’t accept or prefer cash. I carry a notebook where I record my cash transactions then every Sunday I sit down and use the Tiller manual transaction addon to input the cash payments.
The main issue I’ve encountered dealing with foreign transactions is with the volatility of the currency. The difference isn’t that much but the aggregate can cause a large discrepancy.
I haven’t really put that much thought into the process right now but would really like for an addon to be made that can track the cash transaction and the closing or average price for the FX on that day.
Thank you for sharing this @asdf !
It is certainly true that tracking down exchange rates can be difficult and time consuming, but when you find them, these rates won’t be of much help, and you don’t need them. The real rate you pay occurs in just two types of transactions for which you have all the necessary data. This makes it pretty easy to track.
When I use my credit/debit card, my statement (and the subsequent Tiller transaction) will tell me the cost in USD. Comparing the debit to my account with the price I paid in local currency gives me the exchange rate. This rate will probably vary with each transaction, or at least each day’s transactions. In either case I can factor the exchange rate, as long as I’ve made a note of the local purchase price.
When I exchange USD for local currency either at an airport, bank, or ATM, a similar event occurs. If I exchange $100 USD for South African rand and receive 9,274 ZAR, there’s my real exchange rate: 100:9724. Thereafter, as I spend that 9,274 ZAR, I know each rand cost me about 11 cents. Any fees get factored into the exchange rate. Of course, if I hit the ATM again, that real cost may change for the next wad of ZAR, and I have to keep track in my notebook.
Since Local Amount = Amount (USD) x Exchange Rate, and vice versa, you can calculate the missing value.
While it is possible to get official closing exchange rates, and use them in your spreadsheets, this approach will only approximate your circumstances, and almost never match real cost to you
Good points @GregC 
it’s a layered issue!
@GregC I think I follow what you’re saying.
Ideally, my intention is to accurately track all my expenses and income in one currency, which is USD. Sometimes I get paid and use foreign cash for transactions that I need to track. Sometimes that cash is from an ATM withdrawal and sometimes I’ll get paid in cash. Since the currency transaction is not an isolated event I need to be able to track and convert to USD which doesn’t align with your suggestion.
