I want/need to use currency conversion data from a different date than the order date when making reports

We have been made aware that when physical goods are sold from Northern Ireland to the EU (governed by the special Northern Ireland regulations), then in the resulting OSS report, vendors are expected to use the European Central Bank’s currency conversion rate from the last day of the reporting quarter, and not the conversion rate from the date of the order. (More information available here: https://www.gov.uk/guidance/completing-a-one-stop-shop-vat-return).

This can affect sellers whose shop base address is not Northern Ireland, if, for example, they are based elsewhere but have a warehouse in Northern Ireland.

This is slightly awkward, since of course on the date of the sale itself (unless it happened to be on the very last day of the quarter), that currency conversion rate is not yet known/published, being in the future. This means that the true data for the report due to the order will not be known until the quarter has ended.

If your shop is in this situation, then one way to deal with it is to download the detailed CSV download (which has a line for every order) into your spreadsheet program. This CSV can be fetched from the “Reports” tab of the plugin. Then, find the column for the conversion rate, and replace it with the proper ECB exchange rate. If this issue only applies to some orders and not to others, then you will need to take further steps (i.e. filter down to, and replace only, the affected orders).

You can also use the following code (as a WordPress “mu-plugin”) to automatically apply this conversion to data in the “Reports” tab (including CSV downloads) for any reports from past quarters (i.e. not the current, open quarter). To do so, follow these steps:

  1. Create a directory called “mu-plugins” in your wp-content folder in your WordPress installation. i.e. The name of the folder should be wp-content/mu-plugins
  2. In that folder, create a file whose name is of your choice, but which ends in .php, e.g. wp-content/mu-plugins/change-currency-rate-date.php
  3. In that file, insert the content of the code shown below. If when you reload your site there is a white screen, then you introduced an error, and should remove the file and try again.

Note that it is assumed that in your plugin settings (in the “Settings” tab) you have chosen the European Central Bank to use for your currency conversion rates. N.B. This code fragment requires version 1.27.6 or later of the plugin and will not work in any earlier version.

<?php

// Do not use conversion rates stored with the order at order time
add_filter('wc_vat_compliance_currency_converted_order_data_use_order_saved_data', '__return_false');

// If the order is from a previous quarter, then use the date of the end of the quarter for looking up the desired conversion rate
add_filter('wc_vat_compliance_currency_converted_order_data_use_conversion_time', function($time, $raw, $order_currency, $conversion_rates, $get_items_data_for_order, $refunds_data_for_order, $reporting_currency) {

  if ('EUR' !== $reporting_currency || !isset($raw['date_gmt']) || !preg_match('/^(\d+)-(\d+)-(\d+) /', $raw['date_gmt'], $matches)) return $time;

  $order_year = (int) $matches[1];
  $order_month = (int) $matches[2];

  $end_of_quarter_year = $order_year;
  $end_of_quarter_month = ($order_month-1) + 3 - (($order_month-1) % 3);

  $end_of_quarter_day = cal_days_in_month(CAL_GREGORIAN, $end_of_quarter_month, $end_of_quarter_year);

  $end_of_quarter_epoch_time = mktime(12, 0, 0, $end_of_quarter_month, $end_of_quarter_day, $end_of_quarter_year);

  // If this is still future, then do not filter the value - only filter for past quarters
  if ($end_of_quarter_epoch_time > time()) return $time;

  return $end_of_quarter_epoch_time;

}, 10, 7);

Posted in: WooCommerce EU/UK VAT Compliance