Uploads: Earnings in different currencies

[g105b]

This code is basic GCSE maths, but because I was too busy discussing Tomb Raider 2 cheat codes rather than listening to how basic multiplication works, I had to write this code out longhand.

What this code does is take the actual earnings as provided by Believe Records, and the actual money that hit Biff's bank account for a period, and calculates the actual exchange rate according to the EUR->GBP conversion, so it can calculate the GBP earnings for each statement.

It also proves the numbers add up, so I know I'm not completely bonkers.

In TS uploads table, there will be multiple statements. When the user enters the amount credited in their local currency, they must select which uploads the credit applies to.

This code proves that the amount received in foreign currencies can easily be converted to the correct amount for the correct period when presented with a UI that makes sense of all this.

$earningsInQuarter = [
    "q3_2022" => 79.23,
    "q4_2022" => 74.63,
    "q1_2023" => 56.70,
    "q2_2023" => 57.08,
];
$totalEurosEarned = 267.64;
$actualTotalEurosEarned = array_sum($earningsInQuarter);

$gbpReceived = 225.03;
$bankStatedExchangeRate = 1.1894;
$actualExchangeRate = $actualTotalEurosEarned / $gbpReceived;

echo "Total: EUR $totalEurosEarned, actual total: EUR $actualTotalEurosEarned\n";
echo "Actual withdrawal received: GBP $gbpReceived\n";
echo "Exchange rate: $bankStatedExchangeRate, actual exchange rate: $actualExchangeRate\n";

echo "\n";

$gbpEarnings = [];
foreach($earningsInQuarter as $quarter => $euroEarning) {
    $gbpEarning = $euroEarning * (1 / $actualExchangeRate);
    $gbpEarnings[$quarter] = $gbpEarning;

    echo "$quarter \t EUR $euroEarning \t= GBP $gbpEarning\n";
}

$totalPoundsEarned = array_sum($gbpEarnings);

echo "\n";
echo "Total GBP earned: GBP $totalPoundsEarned\n";

Output:

Total: EUR 267.64, actual total: EUR 267.64
Actual withdrawal received: GBP 225.03
Exchange rate: 1.1894, actual exchange rate: 1.1893525307737

q3_2022      EUR 79.23  = GBP 66.616077193245
q4_2022      EUR 74.63  = GBP 62.748426617845
q1_2023      EUR 56.7   = GBP 47.672997309819
q2_2023      EUR 57.08  = GBP 47.992498879091

Total GBP earned: GBP 225.03

[richardbirkin]

What I remember from our discussion last week...

The most simple version

A user with statements in one currency, can view their earnings in that currency.

e.g. Gringo = GBP, PITP = USD.

This version of the feature enables both labels to view their earnings in the correct currency.

This is the one we do now, as it completes the product for a large number of users.

The interim version

A user with statements of different currencies should be able to see a product's earnings totals in whatever currencies those earnings arrive in.

e.g. Biff's Album

earnings via Bandcamp (GBP): £100 earnings via Distrokid (USD): $50

Could display as:

Biff | Biff's Album | £100 + $50

This is far from ideal, but at least it shows the correct amounts.

The challenging version

A user with statements in multiple currencies can view their earnings in a single currency.

E.g. Me: Bandcamp: GBP Believe: EUR CD Baby: USD

I don't know how common this is, but am confident that it will be the case for any non-US artist/label who uses one of the top digital distributors (Distrokid, CD Baby, Tunecore) as they all pay out in USD, which is then converted by bank on international transfer.

I have done a mockup of this as a wireframe where different currency earnings are displayed on the product. A user can then convert them into their currency by entering how much hit their bank account against the corresponding uploaded statement. The product's % of that USD amount will then become the same % of the reconciled GBP amount that the user entered.

This feels clumsy and onerous. However, we don't want to get involved in estimating the exchange rate.

[richardbirkin]

[g105b]

In response to the PayPal example, it shows two ways of displaying this information: one is by listing out the individual currencies, and the other is estimating the primary currency value.

I don't think that displaying the multiple currencies works in a table format, but we could use a lookup table to estimate the value of the primary currency.

This would change your example from:

Biff | Biff's Album | £100 + $50

To:

Biff | Biff's Album | £132.71*

The £132.71 would be marked as estimated due to it being calculated from a known, but probably not accurate, conversion chart.

This functionality is actually a combination of both interim and challenging...

If we mark the earning in the database as an estimate, we can display it in the UI with some kind of icon. Clicking the icon can take the user to the payment screen for that statement, which will complete the challenging version of this feature:

1. The user uploads a statement in GBP. All Products are created with their accurate earnings in actual GBP.
2. The user uploads a statement in USD. TrackShift uses an API to get a reasonably accurate exchange rate for the day in question. This earning is then saved to the database, but an "original earning" is saved alongside, specifying the original currency it was uploaded in. The presence of an "original earning" will indicate that the earning's value is estimated. We can still store the value in the appropriate field in the database, so existing calculations are kept working, but we can easily show that the value is estimated from our own lookup chart.
3. When the user receives payment into their GBP bank account, they enter the amount received in pounds and associate it to an uploaded statement.
4. If the statement was worth $1000 USD, and the actual amount received was £100 GBP, each earning can then be calculated as a pro-rata payment. For instance, an earning for a product of $10.00 is calculated to be 1% of the value of the statement, so can be accurately converted to be 1% of the amount earned in GBP. The earning value in the database can be accurately set to £0.70 and the "original earning" can be removed so it is no longer indicated as an estimated value.

[richardbirkin]

@g105b I'm struggling to test this with the data available due to the importer not working on some reports. Across these there aren't any that are working that feature the same product(s) in different currencies.

RJB Data Bandcamp (GBP): Not Importing Believe (EUR): Not Importing CD Baby (USD): Importing

Crash of Rhinos: Bandcamp (GBP): Not importing Route Note (USD): Not importing

PITP Data is all USD

Gringo Data is all GBP

Other Label Bandcamp (GBP): Not importing Tunecore (USD): Importing Proper (GBP): Not importing

[richardbirkin]

Bandcamp: F currency Believe: W Client Payment Currency Cargo Digital: default GBP (until we find out if they pay out in different currencies depending on the client) Cargo Physical: default GBP (until we find out if they pay out in different currencies depending on the client) CDBaby: Default USD Distrokid: Default USD Octiive: Default USD PRS: Default GBP RouteNote: Default USD Tunecore: Default USD