## Dataset overview (ppea2023058-s002)

## Source details

**Canonical URL:** [Dataset overview (ppea2023058-s002)](https://www.imf.org/-/media/files/publications/pp/2023/english/ppea2023058-s002.xlsx)

## Other formats

- [Markdown version](/-/media/files/publications/pp/2023/english/ppea2023058-s002.xlsx.md)
- [Structured JSON version](/-/media/files/publications/pp/2023/english/ppea2023058-s002.xlsx.json)

---

### Purpose and access
- Designed to retrieve Refinitiv Eikon FX market data (spot, forwards, options, and rates) and to apply MCP (Market Conduct Policy) checks and theoretical calculations.
- Connect to Refinitiv Eikon: "Log into your Refinitiv Eikon account."
- Time window behavior:
  - "The start date and end date are set up by functions, which will include days between yesterday and back one year from then."
  - Start Date formula example: "EDATE(B6,-12)" with result "2023-02-19T00:00:00.000Z".
  - End Date formula example: "TODAY()-1" with result "2024-02-19T00:00:00.000Z".
- Data-sharing constraints (verbatim):
  - "WHILE INTERNAL SHARING OF RETRIEVED DATA IS PERMISSIBLE TO AN EXTENT WITHIN THE PARAMETERS OF THE IMF'S CONTRACTUAL AGREEMENT WITH REFINITIV EIKON*, STAFF MUST ABIDE BY EXTERNAL SHARING PERMISSIONS. ONLY NON-SYSTEMATIC SHARING OF A LIMITED AMOUNT OF DERIVED DATA WITH AUTHORITIES IS PERMITTED. PLEASE CONTACT JOINTLIBCONTENT@IMF.ORG FOR FURTHER INFORMATION ON DATA SHARING PERMISSIONS."
  - Additional note (verbatim): "*There is a limited number of Refinitiv Eikon licenses for use at the IMF, and account credentials should not be shared, however, under the IMF’s license agreement with Refinitiv, account holders are permitted to share portions of data internally with IMF colleagues working on the same project and store the same in a shared drive. Daily values of particular countries’ exchange rates over a 12-month period fall under the definition of “portions of data” for the purpose of this project."

### Worksheets and key contents
- Instructions (rowCount 16, columnCount 12)
  - Contains connection, time-zone, date guidance and data-sharing permissions text.
- RICs (rowCount 488, columnCount 7)
  - Columns: "RIC", "Name", "Instrument Type", "Currency".
  - Sample RICs (exact strings):
    - "AFN" — "US Dollar/Afghanistan Afghani FX Spot rate" — "FX Spot Rates" — "US Dollar"
    - "ALL" — "US Dollar/Albanian Lek FX Spot Rate" — "FX Spot Rates" — "US Dollar"
    - "DZD" — "US Dollar/Algerian Dinar FX Spot Rate" — "FX Spot Rates" — "US Dollar"
    - "AOA" — "US Dollar/Angolan Kwanza FX Spot Rate" — "FX Spot Rates" — "US Dollar"
    - "XCD" — "US Dollar/East Caribbean Dollar FX Spot Rate" — "FX Spot Rates" — "US Dollar"
    - "ARS" — "US Dollar/Argentine Peso FX Spot Rate" — "FX Spot Rates" — "US Dollar"
    - "AMD" — "US Dollar/Armenian Dram FX Spot Rate" — "FX Spot Rates" — "US Dollar"
    - "AUD" — "Australian Dollar/US Dollar FX Spot Rate" — "FX Spot Rates" — "Australian Dollar"
    - "EUR" — "Euro/US Dollar FX Spot Rate" — "FX Spot Rates" — "Euro"
    - "AZN" — "US Dollar/Azerbaijan Manat FX Spot Rate" — "FX Spot Rates" — "US Dollar"
- BBG_H_L (rowCount 19, columnCount 3)
  - Example currency cell: "AFN Curncy"
  - Start Date linked to "Spot!B5" result "2023-02-19T00:00:00.000Z"
  - End Date linked to "Spot!B6" result "2024-02-19T00:00:00.000Z"
  - Example Bid/Ask sample numeric rows showing integers 12, 13, 14, 15 and corresponding computed values 24, 25, 26, 27 in sampleRows.
- TR_High and TR_Low (rowCount 895 and 894)
  - Procedures to change ISO currency code in cell A2 (instruction string preserved).
  - Sample formula results: "You do not have permission to view these RICs: 0".
  - Start Date "2023-02-19T00:00:00.000Z", End Date "2024-02-19T00:00:00.000Z".
- Spot (rowCount 765, columnCount 23)
  - Columns include Date, High (Ht), Low (Lt), R (official rate), M H/L, M H/L +2%, M H/L -2%, MCP test, Total findings, Days since last MCP, Date Last MCP, and time zone strings.
  - Time Zone examples: "(GMT -11:00) APIA API", "(GMT -10:00) HONOLULU HON", "(GMT - 9:00) ALASKA ALS", "(GMT -8:00) LOS ANGELES LAX", "(GMT -8:00) SAN FRANCISCO SFR".
  - MCP logic examples (formulas preserved):
    - "IF(AND(ISNUMBER(D8),ISNUMBER(B8)),(IF(OR(AND(D8>G8,D8>B8),AND(D8<H8,D8<C8)),\"MCP finding\",\"no MCP\")),\"\")"
    - "COUNTIF(J:J,\"MCP finding\")"
    - "TODAY()-INDEX(A:A,MATCH(\"MCP finding\",J:J,0))" (result shown as an error "#N/A" in sample).
- Non-Spot (rowCount 266, columnCount 32)
  - Supports custom search "Input or Custom Search" and tenors (e.g., "ON", "TN", "2W", "3W", "6W", "1M", "2M", "3M").
  - MCP logic analogous to Spot with formulas using columns K:M,O:Q,S and time zone strings identical to Spot examples.
  - Example formula results: "Invalid RIC(s): =" for RHistory calls in sampleRows.
- Theoretical Non-Spot - Forwards (rowCount 765, columnCount 46)
  - Columns include Domestic Rate, Foreign Rate, notes on dividing interest rates by 100, and RHistory formulas for yields.
  - Sample formula: "IFERROR((B8+C8)/2,\"\") * ((1+D8)/(1+E8))" with result error "#VALUE!" in sampleRows.
  - RHistory formula results shown as "Invalid RIC(s): =" and "No instrument defined." in sampleRows.
- Theoretical Non-Spot - Options (rowCount 762, columnCount 13)
  - Columns: Date, Strike, Premium, R (official rate), M H/L, M H/L +2%, M H/L -2%, MCP test, Total findings, Days since last MCP, Date Last MCP.
  - Date adjustment formula example: "IF(WEEKDAY(B3)=1,B3-2,IF(WEEKDAY(B3)=7,B3-1,B3))" with result "2024-02-19T00:00:00.000Z".
  - Rolling-date formulas using weekday logic and shared ranges for repeated calculations.
- EER - Import & Deposit (rowCount 30, columnCount 26)
  - Title: "Effective Exchange rate of transaction ( R )"
  - Columns sample include Date, Market (High), Market (Low), i^k, i^g, G, T, M H/L, R, M H/L +2%, M H/L -2%, MCP Assessment.
  - Numeric examples:
    - A sample row with formulas and numeric results:
      - Date (formula "TODAY()-1", result "2024-02-19T00:00:00.000Z")
      - Market (High) 75
      - Market (Low) 76
      - i^k 0.14
      - i^g 0
      - G 1
      - T 1
      - "AVERAGE(P3:Q3)" result 75.5
      - "V3*(1+(R3-S3)*T3*U3)" result 86.07000000000001
      - "1.02*V3" result 77.01
      - "0.98*V3" result 73.99
      - MCP assessment formula result: "MCP finding"
    - Additional rows show variations:
      - Row with "TODAY()-2" result "2024-02-18T00:00:00.000Z" and computed "V4*(1+(R4-S4)*T4*U4)" result 82.295 with MCP finding.
      - Row with "TODAY()-3" result "2024-02-17T00:00:00.000Z" and computed "V5*(1+(R5-S5)*T5*U5)" result 77.01 with MCP outcome "no MCP".
  - Definitions included (verbatim):
    - "Amount of domestic currency required by official action to be deposited to obtain one unit of foreign currency"
    - "Annualized domestic currency market interest rate."
    - "Annualized rate at which the cash margin is remunerated"
- Official Rate - Haver (rowCount 105, columnCount 4)
  - Columns: "Curn code", "Haver Code", "Code", "Units"
  - Sample rows (exact strings):
    - "AED" — "x466usb@INTDAILY" — "466" — "Dirham/US$"
    - "ALL" — "x914usb@INTDAILY" — "914" — "Lek/US$"
    - "AMD" — "X911USB@INTDAILY" — "911" — "Dram/US$"
    - "AOA" — "X614USM@INTDAILY" — "614" — "Kwanza/Dollar"
    - "ARS" — "x213usb@INTDAILY" — "213" — "Argentinian Peso/US$"
    - "AUD" — "x193usb@INTDAILY" — "193" — "US$/A$"
    - "AZN" — "X912USB@INTDAILY" — "912" — "New Manat/US$"
    - "BAM" — "X963USB@INTDAILY" — "963" — "CMarka/US$"
    - "BDT" — "X513USM@INTDAILY" — "513" — "BDT/USD"
    - "BGN" — "X918USB@INTDAILY" — "918" — "Lev/US$"

### Computation logic and MCP checks
- MCP detection logic is implemented across worksheets with identical conditional structures (preserve formulas exactly):
  - Spot MCP check formula example: IF(AND(ISNUMBER(D8),ISNUMBER(B8)),(IF(OR(AND(D8>G8,D8>B8),AND(D8<H8,D8<C8)),"MCP finding","no MCP")),"")
  - Non-Spot and Forwards/Options use analogous IF/OR/AND constructs and COUNTIF to tally "MCP finding".
- Permissible margin calculations:
  - Typical permissible band computed as M = AVERAGE(high, low) and margins as 1.02*M and 0.98*M (formulas preserved).
  - For forwards theoretical rate example: IFERROR((B8+C8)/2, "") * ((1+D8)/(1+E8)) (sample produced "#VALUE!" in sheet).

### Example numeric and date values preserved from sample rows
- Start Date sample: "2023-02-19T00:00:00.000Z"
- End Date sample: "2024-02-19T00:00:00.000Z"
- Spot sample time-zone strings: "(GMT -11:00) APIA API", "(GMT -10:00) HONOLULU HON", "(GMT - 9:00) ALASKA ALS", "(GMT -8:00) LOS ANGELES LAX", "(GMT -8:00) SAN FRANCISCO SFR"
- EER sample numeric values:
  - Market (High) 75
  - Market (Low) 76
  - i^k 0.14
  - i^g 0
  - G 1
  - T 1
  - AVERAGE(P3:Q3) result 75.5
  - V3*(1+(R3-S3)*T3*U3) result 86.07000000000001
  - 1.02*V3 result 77.01
  - 0.98*V3 result 73.99
  - MCP assessment result "MCP finding"

### Usage notes and error indicators
- Several RHistory calls in sampleRows returned textual error indicators:
  - "You do not have permission to view these RICs: 0"
  - "Invalid RIC(s): ="
  - "No instrument defined."
- Some computed formulas in sampleRows resulted in spreadsheet error tokens:
  - "#NAME?"
  - "#VALUE!"
  - "#N/A"
- Many cells use shared formulas and range references (examples: "ref": "B11:B19", "ref": "C10:C19", "shareType": "shared", "sharedFormula": "C10", "ref": "P6", "shareType": "array").

*Source: ppea2023058-s002 (XLSX dataset) — canonical file ppea2023058-s002*

---


_Source: https://www.imf.org/-/media/files/publications/pp/2023/english/ppea2023058-s002.xlsx_