Separating CDN from USD within the Same Sheet?

I have US and CDN bank accounts. I didn’t realize until I loaded them all together that there’s no way to denominate the transactions that are CDN or USD.

I’m fairly new to Tiller, but it’s created a huge disorganization for my taxes, as I file separate returns.

Any thoughts?

If all you want to do is differentiate which ones are CDN, and which ones are USD, you could probably use Tags. If you actually want some conversions done, then it’ll be a much more complex solution.
Here’s one way to add tags: Add the Tags Report for Transaction Tagging - Wikis & Webinars / Tiller Labs - Tiller Money Community (tillerhq.com)

I can’t promise an availability date, @joelholmes92, but I know @brasten, @tom & @heather are working on a story to expose the currency type through the feeds-update process. Hoping there is more data available to address this need in the coming months.

1 Like

Hi Joel, I have the same problem and I’ve been using an awkward workaround but I’m looking for a less manual solution.

At the moment, all the accounts come into the same transactions tab. For my US transactions only, I cut and paste the number in the default ‘Amount’ column into a column I created called ‘US Amount’. I then paste a formula into the now empty ‘Amount’ column that multiplies the ‘US Amount’ by the exchange rate (which I input monthly onto another tab) to give me an approximate CAD value of all the transactions in the ‘Amount’ column.

Any other ideas would be much appreciated!

An interesting problem. Here’s how I’m thinking I would do it. I would add two new columns, one called ‘Tags’ (if you don’t already have one), the other called ‘RealAmount’.

The tags column can be used for all sorts of useful stuff, but in this case, I’d put “USD” in any rows that were US transactions (if you need to use other tags for other things, separating them with a comma would probably work depending on how the other function checks for tags). In the ‘RealAmount’ column I’d put the following formula:
=IF(REGEXMATCH(E2,"USD"),INDEX(GOOGLEFINANCE("CURRENCY:CADUSD", "price", A2),2,2)*D2,D2)

It does the following (assuming you’re columns are laid out as mine are in the image below):

  • If the ‘Tags’ column does not contain ‘USD’ it will list the original amount.
  • If the ‘Tags’ column does contain ‘USD’, it will look up the exchange rate using Google Finance for the date of the transaction, multiply it by the amount, and list the ‘Real’ amount. Not sure if I have the right exchange listed, you might have to use USDCAD depending on which way you need to convert.

You’d need to ‘fill’ the formula up/down the column for each new row as they are created (GoogleFinance doesn’t work in an Arrayformula, so can’t use that option),. You could then hide the ‘Amount’ column, and just show the ‘RealAmount’ column. Seems like it should do what you want, give it a try!
Screenshot 2021-06-06 185740

2 Likes

This is an awesome solution @jpfieber, best part is you could use AutoCat to automate the USD tag based on the Account name or number! Would the “real amount” formula still work if you had multiple tags in the same row separated by commas?

1 Like

Great idea! That would work, but I suppose only if “USD” is the only automated tag (don’t think AutoCat can ‘append’). Yes, the REGEXMATCH will pull “USD” from a list of other tags, so a great way to use an existing column for this. The user would still need to copy the formula to the new transaction, unless AutoCat could put the formula there? I can’t test it at the moment, but if AutoCat could put the formula into the cell, the Tag might not even be needed.

We don’t currently have a way of writing formulas via Autocat. :slightly_frowning_face:

I imagine it would be possible as a special case in the code. Right now, formulas in the Autocat sheet are rendered to values before they are written into the Transactions sheet.

Just to make sure we don’t miss a use case — clearly currency codes are important here. Is it possible that feeding accounts with different currencies into different transactions sheets would also be helpful? A US Transactions and CND Transactions, for example?

If there were two separate trxn sheets for each currency @brasten, how would a visualization work to pull data from both of them if you want a single budget vs a CND budget and a USD budget - that seems overly complicated and not in line with our efficiency promise?

:thinking: outside our current boxes into the types of visualizations you envision for the future… :ramen:

It’s a good question. To be honest I don’t really understand how a single budget visualization across multiple currencies would even work all that well. There are ways to do it I’m sure. You can KIND of get away with it with USD/CAD because the same numerical representation for the same “value” is roughly close enough to get by. But that’s just a happy accident.

So I guess my question was making the assumption that one would either have separate budgets for separate currencies, or our “budgeting solutions” would have to handle that in some way. And of course nothing in my question should imply that people who prefer the way it is now would need to do anything different.