We track expenses for our trips. We use an Excel spreadsheet. I always have my PC for various reasons. I posted this about 3 years ago, but am putting it back up as we are planning a trip in Sept-Oct, and I need to remember this stuff.
ORGANIZATION: EXPENSES
A-Date
B-Expense type (taxi, dinner, tip, train tix)
Next cols are pairs
C-USD Cash
D-USD CC
E-Euro Cash
F-Euro zone CC
G-Croatian Kuna Cash
H-Croatian Kuna CC
Continue adding columns in the same pattern as you have new currencies. You need to separate credit-card from cash to allow you to reconcile at the end of the day.
ORGANIZATION: WITHDRAWALS
I put withdrawals in Col AA or so. There are not many of these, so things are a little less organized. I just put
AA: Currency
AB: Date
AC: Amount
COMPUTING
Arithmatic in Excel or any spreadsheet is simple.
=C12+D13 - adds C12 and D13
=sum(c5:c10) - adds all cells C5, C6, C7, C8, C9, C10
Reconciling at the end of the day is equally simple. Take money from wallet, count.
Put in a specific location. To reconcile, use the following and put it in a known location.
=AF12-sum(C4:C2000)-AF13
I always sum to a big number like 2000 or 3000 because that way you don't leave out rows. Excel considers a blank cell to be 0.
AF12: Original withdrawals from ATM
AF13: Current amount in wallet
This will be 0 if you have counted your wallet right (trust me, this is easy to mess up) and not forgotten purchases or the .1 E coin you gave to the musician. In terms of long term importance, I don't put a huge stock in that. I merely try to reconcile at the end of the day. That way, you keep a complete record of the spending.
ADDING ACROSS CURRENCIES
To add up across currencies, something like the following is needed:
=sum(c3:c2000)+sum(D3:d2000) + (sum(e3:e3000)+sum(f3:f3000))/1.12 + (sum(g3:g3000)+sum(h3:g3000))/6.5
This sums USD (Cols C and D), Euros (Cols E and F) and Croatian Kuna (Cols G and H) and applies conversion factors. At the end of the trip, we have a complete expense record.
NEW SECTION: COMPUTING CATEGORY TOTALS
You can use the Excel function SUMIF to compute totals for categories. I added a new column B for the overall category, and use a letter (B for food, C for hotels, G for activities, etc). To compute one of these, the hotel total, use this function:
=SUMIF(B2:B3300,"C",D2:D3300)+SUMIF(B2:B3300,"C",E2:E3300)+(SUMIF(B2:B3300,"C",F2:F3300)+SUMIF(B2:B3300,"C",G2:G3300))*1.12+(SUMIF(B2:B3300,"C",J2:J3300)+SUMIF(B2:B3300,"C",K2:K3300))/6.5
That is for 3 currencies - USD (Col D and E), € (Col F and G), Croatian Kuna (Col J and K). More functions can be added. I'm looking for a way to loop through this.
Some will ask "Why?" Well, for my wife and myself, we like to know where the money goes, and how much we are spending each day.