Recently, I took a ski trip with a few consulting friends to Chamonix, stopping in Geneva on the way (and Berlin after). We used TripSplit to settle, but ran into an issue with our expenses being in multiple currencies. Typically, my international trips have only been to one country (and thus currency) (e.g., Mexico and Pesos), so expenses can just be divvied in that currency and converted after.
Since our trip touched on 3 currencies (Swiss Francs, Euros, and USD for the AirBNB), we ran into a bit of an issue translating those into USD. We ended up just converting before entering it into the spreadsheet (~1.1 for Euros, flat for Francs), but that was inconvenient. To change that, I updated the spreadsheet to include a "currency" field for each line item, with a simple vlookup to grab the exchange rate.
Locking in an Exchange Rate
I toyed with two options for entering that exchange rate, manual and automatic.
Automatic was the easier option, but it ran into issues as it would change daily (or more frequently), so depending on when you settled, it would change how much you owed. While currency fluxuations are usually fairly minor, I happened to be in Argentina during the most recent presidential turnover (December 2015), with a 30% jump in one day, so was wary of that issue.
Likewise, I wanted it to be slightly automated to not force people to look up exchange rates, so a full manual approach was out.
My compromised approach is to automatically pull down the current exchange rates (shoutout to fxexchangerate.com for their unwitting participation), but write a brief google sheets script to lock in a given rate*, thus avoiding currency fluxuations. It also updates the date of the "pull" to make sure it isn't too far off. I'll leave it to each group to decide when to lock in rates.
I was unable to figure out how to add buttons to call the function as with VBA macros in Excel, but turns out there's a nifty onOpen() function in Google App Scripts, which I used to add a one-item menu to the Google Sheets toolbar.
*Using a glorified paste values
- Keep working on mobile friendly version
- Potentially incorporate a rolling 7 day exchange rate