Add Currency conversion

[dave-mills]

Fixes #77

This PR adds the extra features required to let users enter initiative budgets in any currency, and add an exchange rate to let the platform convert the budget into a single per-organisation currency.

Notes on Requirements

• Each institution has a "default currency". Right now, it does not matter if each institution has different default currencies.
• Users can enter initiative budgets in any currency.
• The institution level dashboard should present the budgets in the institution's 'default' currency.

Implimentation

Retrieving data from the API

The platform is linked up to the service at https://freecurrencyapi.com/. Instead of making requests each time to the API, the platform has a set of commands:

• GetExchangeRates: This is a one-time command that will get the historic exchange rates from every day between 2000-01-01 until yesterday, for each of the 32 supported currencies. This information is stored in the platform database, ready to be quickly retrieved when a user asks for the exchange rate.
• GetYesterdaysExchangeRates: This is a command that will run daily, to retrieve the latest exchange rates for the 32 supported currencies.

This way, we can get all the needed information with the same number of API calls, regardless of how large the user base grows.

Storing the data

The platform now includes data models and tables for currencies and exchange_rates.

Creating an Initiative

The user is asked for the initiative currency, and then has the option of automatically retrieving the exchange rate:

• If the exchange rate is found, the field is populated automatically.
• If the exchange rate is not found, the user is shown an alert and asked to enter the exchange rate manually.

If the user has entered a start_date and that date is in the past, that date is used to retrieve the exchange rate. Otherwise, the latest exchange rate is used.

Limitations of this approach and possible future upgrades

1. Only 32 of the most common currencies are supported by the free API.

• I think this is fine for now. A possible future enhancement would be to subscribe to the paid API, and if the user enters a less common API, we can get the exchange rate directly from the API service.

2. The Free API does not give "today's" rates. Only yesterday's.

• Again, this is probably fine for our purposes. The automatic exchange rate feature is not intended to be 100% accurate, but is intended to give a good estimate of the budget in the institution's currency. Users can override the exchange rate if they know exactly what rate is used for the real funding.
• If this becomes an issue, we have the option to upgrade to the paid API and retrieve more recent rates.

3. No 'single' currency

Right now, the budgets are converted to individual institution's currencies. This is fine for the institution level. Eventually, the goal is to get an overview of funding flows towards Agroecology across the entire system, which means converting the budgets of all inititives into a common currency (likely EUR).

This is probably an enhancement to add in a future version of the platform.

[dave-mills]

To facilitate testing, I have added:

• GetExchangeRatesForTesting - a separate command that lets you retrieve entries from the API for a single year. (use = `php artisan app:get-test-exchange-rates 2023);

• A db seeder containing all the entries for 2023.

One possible issue with this approach is that we will end up with: (32 32 365) = ~400,000 records per year. I assume the database will cope with this, but it's worth testing that.

I am going to run the full set of API Jobs locally over night to review this.

[dan-tang-ssd]

When creating a new initiative, maybe we should ask user for project start date and end date first before asking about currency and budget. So that exchange rate can be retrieved for a specified project start date.

[dave-mills]

When creating a new initiative, maybe we should ask user for project start date and end date first before asking about currency and budget. So that exchange rate can be retrieved for a specified project start date.

Already fixed in #104 - merged in because it affected the recording of the training videos.

[dan-tang-ssd]

For exchange rate API, do we have new item to add in .env file? I cannot run the command for getting exchange rate history...

[dave-mills]

Yes. I forgot to add it into the .env.example file. But, if you read the changed code, you'll find reference to:

config('services.currency.api-key'), which leads to =>

'currency' => [
        'api-key' => env('CURRENCY_API_KEY'),
    ],

in the services.php config file ;)

[dave-mills]

I've also put the API key in 1Password: (Freecurrency API in the Stats4SD IT vault) https://share.1password.com/s#gHoMb19Smkta9s6yKWO6GK_8yaCFkKZdudZXKIxGZHw

[dan-tang-ssd]

I added new item for free currency API key in .env.example file.

I also updated .env with API key stored in 1Password, it works fine now.

When we have more and more exchange_rates record, we may need to add index for base_currency_id, target_currency_id and date to avoid possible performance issue.

[dan-tang-ssd]

I performed testing in front end, by creating a project with budget HKD 1. Suppose we should have EUR 0.11xxx, But the converted EUR amount is incorrect. It looks like the FROM currency and TO currency after interchanged...

[dave-mills]

When we have more and more exchange_rates record, we may need to add index for base_currency_id, target_currency_id and date to avoid possible performance issue.

Indeed - see database/migrations/2023_06_28_133458_add_indexes_to_exchange_rates.php.

It looks like the FROM currency and TO currency after interchanged...

Hmm, yes. Fixed in the latest commit, so now the initiative currency is the "base" and the institution currency is the "target".

[dan-tang-ssd]

It works fine now. I think it is good to deploy.