Monthly treasury FX report

Daily ECB rates, monthly averages and statistics against the budget rate

Download template

Sample data

Template
DOCX
Export as
PDF, DOCX, ODT, HTML, PNG +25 more
Carbone
5.0+

Generate treasury FX reports from your market data

Group treasury teams send the month's exchange rates to the CFO and the risk committee: the closing rate for the balance sheet, the monthly average for the income statement (an average rate that IAS 21 accepts when rates do not fluctuate significantly), and the gap with the budget rate. Your treasury management system or market data feed sends the daily rates to the Carbone API as JSON, and a DOCX template draws native Word charts and fills the statistics table.

In this example, Arvenne Industries, a fictional European group with US dollar sales, reports EUR/USD for October 2026. The data holds the real euro reference rates of the European Central Bank from January to October 2026; the budget rate, the hedging figures and the commentary are fictional.

In this example, you will:

  • Draw a line chart from the daily rates, on a date axis
  • Draw a column chart from the monthly averages, with the budget rate as a line
  • Print the last item of an array
  • Compute changes and gaps to the budget in the template

How to draw FX charts from JSON in a Word report

Follow these steps to add native charts and statistics to your report:

1. Prepare the JSON Data

Send the daily rates, the monthly averages, the budget rate and the statistics of the month and of the year to date. Your system computes the statistics from the same daily series: the opening rate (the last rate of the previous period), the high and low with their dates, the average, and the volatility, the annualised standard deviation of daily log returns.

{
  "budgetRate": 1.15,
  "rates": [
    { "date": "2026-01-02", "rate": 1.1721 },
    { "date": "2026-01-05", "rate": 1.1664 }
  ],
  "months": [
    { "month": "2026-01-01", "average": 1.1738, "observations": 21 }
  ],
  "stats": {
    "month": { "openingDate": "2026-09-30", "opening": 1.1355, "high": 1.1298, "highDate": "2026-10-01",
               "low": 1.113, "lowDate": "2026-10-29", "average": 1.1202, "volatility": 0.0528, "observations": 22 },
    "ytd":   { "openingDate": "2025-12-31", "opening": 1.175, "average": 1.1568, "volatility": 0.0558 }
  }
}

2. Add a Line Chart on a Date Axis

Insert a Line chart at the top of the report. Right-click it, select Edit Data in Excel, and define the loop on the rates array:

|                                                               | EUR/USD             | Budget rate                             |
|---------------------------------------------------------------|---------------------|-----------------------------------------|
| {d.rates[i].date:formatD(X):div(86400):add(25569):round(0)}   | {d.rates[i].rate}   | {d.rates[i].rate:print(..budgetRate)}   |
| {d.rates[i+1].date:formatD(X):div(86400):add(25569):round(0)} | {d.rates[i+1].rate} | {d.rates[i+1].rate:print(..budgetRate)} |
  • Word and Excel store a date as a number of days since 30 December 1899. :formatD(X) returns the Unix timestamp in seconds, :div turns it into days, :add moves the origin to 1899 and :round keeps whole days.
  • Right-click the horizontal axis, select Format Axis, choose Date axis, set the major unit to 1 month and the number format to mmm: the axis shows one label per month, whatever the number of rates in the month.
  • :print(..budgetRate) prints the budgetRate attribute found one level above the rates array on every row, which draws the budget rate as a flat line. Format it as a dashed line.

3. Add a Column Chart of the Monthly Averages

Insert a Clustered Column chart and define the loop on the months array:

|                                    | Monthly average         | Budget rate                                 |
|------------------------------------|-------------------------|---------------------------------------------|
| {d.months[i].month:formatD(MMM)}   | {d.months[i].average}   | {d.months[i].average:print(..budgetRate)}   |
| {d.months[i+1].month:formatD(MMM)} | {d.months[i+1].average} | {d.months[i+1].average:print(..budgetRate)} |

Then:

  • Right-click the Budget rate series, select Change Series Chart Type and choose Line.
  • Add data labels to the columns with the number format 0.0000.
  • Set the minimum of the vertical axis to 1.10, so the gaps between months stay readable.

4. Print the Closing Rate

The closing rate is the last item of the rates array: a negative index reads it without a loop.

  • {d.rates[i=-1].rate:formatN(4)} prints 1.1172: :formatN(4) keeps the 4 decimals used to quote EUR/USD.
  • {d.rates[i=-1].date:formatD('D MMMM YYYY')} prints 30 October 2026.

5. Compute Changes in the Template

Compute the change of the month from the closing rate and the opening rate: {d.rates[i=-1].rate:div(d.stats.month.opening):sub(1):mul(100):formatN(2)}% prints -1.61%. Insert {d.rates[i=-1].rate:div(d.stats.month.opening):sub(1):mul(100):ifGT(0):show('+'):elseShow('')} just before it to add a plus sign to a rise.

The same pattern compares the averages with the budget rate, for example {d.stats.month.average:div(d.budgetRate):sub(1):mul(100):formatN(1)}% for -2.6%. In the statistics table:

  • {d.stats.month.high:formatN(4)} with its date below, {d.stats.month.highDate:formatD('D MMM')};
  • {d.stats.month.volatility:mul(100):formatN(1)}% for the volatility;
  • the same tags with ytd instead of month in the Year to date column.

6. Add the Commentary and the Disclaimer

Write {d.commentary[i].text} in a paragraph and {d.commentary[i+1].text} in the next one: Carbone repeats the paragraph for each comment. End with {d.disclaimer}, the note that states the source of the rates and the internal use of the report.

Tip: set lang to en-gb in your API request to print dates such as 30 October 2026 and numbers with a decimal point.

Save the DOCX template, connect it to your rates, and let Carbone produce the report every month.

Trusted by 800+ paid customers in 40+ countries

Finance
Healthcare
Insurance
Legal
Public
Retail
Energy
Software
Telecom
Dior
Thales
Younited
Danfoss
CMA CGM
Illumina
KparK
Scalingo
French Ministry of the Armed Forces
BMW
Telus
Bestseller
Flagship Biosciences
Insly
Ninox
Sodexo