Currency exchange problem

Question

I'm a website programmer without any knowledge of finance. I'm creating a personal finance website which allows its users to enter their incomes and expenses and generate reports. I'm about to add a feature to my website which allows users to enter their incomes and expenses in any desired currency. So if a user lives in USA and his currency is USD and he goes to a trip to London and buys something and wants to enter that in his account, he may use GBP for that specific expense.

Now the main question. When generating reports with multiple currencies as inputs, what reference currency can I use to convert the amounts. Suppose we call the reference currency GRC (Global Reference Currency) so I can store in my database as an input that at the time this expense was added as 4 GBP, 1 GBP was equal to 1.456 GRC. Is there any special reference that is being used or may I mark every currency as reference myself?

Answer 1

For the purposes of report generation, I would recommend that you present the data in the currency of the user's home country. You could present another indicator, if needed, to indicate that a specific transaction was denominated in a foreign currency, where the amount represents the value of the foreign-denominated transaction in the user's home country Currency.

For example:
Airfare from USA to London: $1,000.00
Taxi from airport to hotel: $100.00 (in £)

In terms of your database design, I would recommend not storing the data in any one denomination or reference currency. This would require you to do many more conversions between currencies that is likely to be necessary, and will create additional complexity where in some cases, you will need to do multiple conversions per transaction in and out of your reference currency. I think it will be easier for you to store multiple currencies as themselves, and not in a separate reference currency.

I would recommend storing several pieces of information separately for each transaction:

1. The Currency type
2. The Amount (denominated in the Currency specified in the first field)
3. The transaction's timestamp
4. The transaction's conversion rate (relative to the user's home currency)

This way, you can create a calculated Amount for each transaction that is not in the user's "home" currency, whereas you would need to calculate this for all transactions if you used a universal reference currency. You could also get data from an external source if the user has forgotten the conversion rate.

Remember that there are always fees and variations in the exchange rate that a user will get for their home country's currency, even if they change money at the same place at two different times on the same day. As a result, I would recommend building in a simple form that allows a user to enter how much they exchanged and how much they got back to calculate the exchange rate.

So for example, let's say I have $ 200.00 USD and I exchanged $ 100.00 USD for £ 60.00, and there was a £ 3.00 fee for the exchange. The exchange rate would be 0.6, and when the user enters a currency conversion, your site could create three separate transactions such as:

USD Converted to £: $100.00
£ Received from Exchange: £ 60.00
Exchange Fee: £ 3.00

So if the user exchanged currency and then ran a balance report by Currency, you could either show them that they now have $ 100.00 USD and £ 57.00, or you could alternatively choose to show the £ 57.00 that they have as $95.00 USD instead. If you were showing them a transaction report, you could also show the fee denominated in dollars as well.

I would recommend storing your balances and transactions in their own currencies, as you will run into some very interesting problems otherwise. For example, let's say you used a reference currency tied to the dollar. So one day I exchange $ 100.00 USD for £ 60.00. In this system I would still have 100 of my reference currency. However, if the next day, the exchange rate falls and $ 1.00 USD is only worth £ 0.55, and I change my £ 60.00 back into USD, I will get approxiamately $ 109.09 USD back for my £ 60.00. If I then go and buy something for $ 100.00 USD, the balance of the reference currency would be at 0, but I will still have $ 9.09 USD in my pocket as a result of the fluctuating currency values! That is why I'd recommend storing currencies as themselves, and only showing them in another currency for convenience using calculations done "on the fly" at report runtime.

Best of luck with your site!