Daily FX exposure digest for open Xero invoices in Slack

Every weekday at 8am, review my open foreign currency invoices in Xero and tell me how my FX exposure has shifted since each invoice was raised, then post the result to Slack.

Treat my Xero organisation's base currency as my home currency, and default to USD if you cannot determine it. Use the Xero List Invoices operation to pull accounts receivable invoices with status AUTHORISED that still have an amount outstanding, then keep only the ones whose currency code differs from my home currency. Filter server side with the where parameter where you can, and page through the full result set rather than stopping at the first page. Use the amount still due rather than the invoice total, so partly paid invoices are measured correctly. Xero returns dates in .NET format like /Date(1551830400000+0000)/, so parse each invoice date into a plain YYYY-MM-DD value before using it.

Get today's rates with the Open Exchange Rates Get Latest Rates operation, and the rate for each invoice date with Get Historical Rates. My plan is USD base only, so do not pass a base parameter: an unsupported base returns a 400 invalid_base error. Instead take the USD quotes and derive each cross rate by division. To convert an amount in currency C into my home currency H, multiply by the USD quote for H divided by the USD quote for C. Do not use the Convert Amount operation, which is Unlimited plan only, and do not use Get Time-Series, which is Enterprise only. Do the arithmetic in the workflow instead. Collect the distinct invoice dates first and make one historical call per date rather than one per invoice, because every call counts against my monthly quota. Note that historical values are the last rate published up to 23:59:59 UTC on the requested day.

For every invoice, compute the outstanding amount in my home currency at the invoice date rate and again at today's rate. The difference is the unrealised gain or loss. For a receivable, a foreign currency that has strengthened against my home currency since the invoice date is a gain, and one that has weakened is a loss. Compute the percentage change as well as the absolute change.

Only call out invoices that have moved more than 2 percent or more than 500 in my home currency, whichever is crossed first, so the digest stays short. Invoices below the threshold still count toward the totals, they just do not get their own line.

Post one message using the Slack Send a Message operation containing three sections. First, the invoices that crossed the threshold, ranked by the absolute size of the gain or loss, each showing the customer, invoice number, currency, amount outstanding, invoice date, the rate then versus the rate now, and the gain or loss in my home currency with a clear plus or minus sign. Second, subtotals of the swing grouped by currency. Third, the net swing across the whole open receivables book since invoice date, including the invoices below the threshold so the total is complete. Keep it scannable and lead with the numbers.

If no invoice crosses either threshold, stay silent apart from a single all clear line, for example: FX check, no material movement across 9 open foreign currency invoices. If I have no open foreign currency invoices at all, say that in one line instead.