Currency / Exchange Rates

Hi, my site allows users (marathon runners) to create a marathon profile page so that they can accept donations from their friends and families.

I have a section on the site called Charities, which shows the amount of money that each charity has received through the site.

The site currently allows users (donators) to donate in USD, EUR, GBP & AUD so each marathon profile page could have a mixture of donations from a mixture of currencies.

There are 30+ charities on my site that the marathon runners can choose to donate their money to at present.

Question
How do i control / calculate the overall total raised for a single charity as there will be a mixture of donations / currencies. A user can choose one of USD, EUR, GBP & AUD as their default currency view in their account, so i can just display the total in one of those, but how do i add all of the donations up.

I was thinking maybe it would be best to just convert all donations for the chosen charity into the users default currency using an exchange rate for each of the 4 available currencies and then manually changing the exchange rates on a daily basis (or pulling them from some exchange rate site)

How do you think this would be best handles, thanks in advance for your advice…

Id say create a separate table just for the totals, and isolate this operation from the rest of you db.

totals_raised
==========
id_ref | USD | UKP | EUR | AUD | locality | total_raised 
----------------------------------------------
23 | 100 | 50 | 30 | 0 | UKP | 16820 <--- (ie ~£168.20)

etc

Run a cron job to grab and update all the totals for charity #23.

This totals “snapshot” could be run daily or hourly.

Then run a cron which grabs the latest FX rates, and update the total_raised column.

That FX rate could be run, hourly or whenever too.

I think that is the way I would handle it.

The bit to watch out for it the second cron must really make sure it separates the fetching of FX data from the table update, maybe curl and get the data, cache it, then check the cache has been updated and then update the table.

So in effect 3 crons …