JobCargaIndicesDiariosUFUSD

Daily UF/USD Index - Bloomberg + Geneva

Date	27/01/2026
Author	Leandro Crespo
Version	1.0

Process Definition

It is a batch integration process that combines data from two sources to calculate the USD/UDI exchange rate (Mexican Unit of Investment). It reads a Bloomberg CSV file from SMB containing the MXN/UDI price, queries the USD/MXN exchange rate from the Geneva database, performs the cross-rate calculation USD/UDI, and generates two CSV output files with different formats for internal systems and Geneva.

Scope

The process includes:

• Reading a Bloomberg CSV file with fixed name (UDI_INDEX_USD.csv) from SMB server.
• Bloomberg format validation (START-OF-DATA / END-OF-DATA markers).
• Query to Geneva database via JDBC to obtain:
  • Calculated load date (previous business day, considering weekends).
  • USD/MXN price via stored procedure and GenevaQuery.
• Extraction of MXN/UDI price from Bloomberg file (instrument containing “MXUDI”).
• Mathematical calculation: USD/UDI = (USD/MXN) ÷ (MXN/UDI).
• Generation of two output records: USD/MXN and USD/UDI.
• Creation of two CSV files with different formats (with/without header, different delimiters).
• Deposit of files in output folders on SMB server.

Inputs

• SMB File: UDI_INDEX_USD.csv located in {{SMB_IN_PATH}} on server {{SMB_HOST}}.
• Expected format: Bloomberg CSV with pipe-delimited structure (|) and START-OF-DATA / END-OF-DATA markers.
• Target instrument: Any instrument containing “MXUDI” in its code (e.g., “MXUDI Index”).
• Geneva Database (JDBC):
  • Load date query (previous business day logic).
  • USD/MXN price query via GenevaQuery with specific filters (PriceList: BL_DL_CCYALL_CLOSENY, Investment: USD, Denomination: MXN).

Outputs

• Internal File: IndDiarioMxUdi_<timestamp>.csv

  • Location: {{SMB_OUT_PATH}}
  • Format: CSV with header, comma delimiter (,)
  • Fields: LoaderType,LoaderAction,Investment,FechaCarga,ListCharge,Denomination,Price
  • Content: 2 records (USD/MXN and USD/UDI)

• Geneva File: IndDiarioMxUdi_Geneva_<timestamp>.csv

  • Location: {{SMB_OUT_PATH2}}
  • Format: CSV without header, semicolon delimiter (;)
  • Fields: same structure as internal file
  • Content: 2 records (USD/MXN and USD/UDI)

• Notification: Email via SMTP API with execution summary (success or error).

Process Flow in Sensedia

The flow is structured in the following stages:

1. Trigger: Quartz Scheduler executes the process Monday through Friday at 07:30 AM.
2. SMB Read: Connection to SMB server and reading of UDI_INDEX_USD.csv file using pollEnrich with 15-second timeout.
3. File Validations:
   • Verification of existence (if not found, terminates without notifying).
   • Conversion to UTF-8 String.
   • Signature validation (discards ZIP/XLSX with “PK” signature).
   • Bloomberg marker validation (START-OF-DATA / END-OF-DATA).
4. Geneva Query - Load Date:
   • Execution of SQL query that calculates the previous business day (if Monday, goes back 3 days; otherwise, goes back 1 day).
   • Output format: yyyy-MM-ddT00:00:00.
5. Geneva Query - USD/MXN Price:
   • Execution of complete stored procedure with Geneva session management (PRC_ConectaGeneva, GenevaQuery, PRC_DesConectaGeneva).
   • Applied filters: PriceList BL_DL_CCYALL_CLOSENY, Investment USD, Denomination MXN, calculated date.
   • Error handling with TRY-CATCH block in SQL.
6. Bloomberg Extraction:
   • Parse of CSV file between markers.
   • Search for instrument containing “MXUDI” in its code.
   • Extraction of date and MXN/UDI price.
   • Logging of all found instruments for debugging.
7. Cross-Rate Calculation:
   • Conversion of strings to BigDecimal (decimal normalization).
   • Calculation: USD/UDI = (USD/MXN) ÷ (MXN/UDI) with 12 decimal precision.
   • Generation of 2 records: one with USD/MXN (obtained from Geneva) and another with USD/UDI (calculated).
   • Price truncation to maximum 20 characters.
8. Output Generation:
   • Construction of internal CSV (with header, comma delimiter).
   • Construction of Geneva CSV (without header, semicolon delimiter).
   • Conversion to UTF-8 bytes for writing.
9. SMB Write: Deposit of both files in their respective output folders.
10. Notification: Sending email with execution result.

Sensedia Configuration

• SMB Connector: Requires file server access configuration:
  • SMB_HOST, SMB_USERNAME, SMB_PASSWORD, SMB_DIR_NAME
  • SMB_IN_PATH (input folder)
  • SMB_OUT_PATH (internal file output folder)
  • SMB_OUT_PATH2 (Geneva file output folder)
• JDBC Connector: ConexionGeneva with access to:
  • dwGeneva database (stored procedures).
  • GenevaSQL database (for GenevaQuery execution).
• Notifications: NOTIFY_TO_EMAIL, PATRIA_NOTIFY_ENDPOINT.
• Input file: Fixed name UDI_INDEX_USD.csv (hardcoded in flow).

Scheduling and Frequency

• Active configuration: Execution via Quartz Cron: 0 30 7 ? * MON-FRI
• Translation: All business days (Monday through Friday) at 07:30 AM.
• Note: There is a commented configuration for execution every 2 minutes (testing).

Notifications

• OK: Email is sent with success summary indicating:
  • Execution ID
  • Name of processed file
  • Names of the two output files generated
• NOT OK: Email is sent with subject “ERROR in IndiceDiarioUFUSD Processing” including:
  • Execution ID
  • File name
  • Specific error detail (file not found, invalid format, Geneva query failure, MXUDI instrument not found, calculation failed, etc.)
• Attachments: No attachments are included in notifications.

Logs and Monitoring

The following events are logged:

• Execution start with exchangeId.
• Attempt to read SMB file (full path and search pattern).
• pollEnrich result (file name, body class).
• Format validations (file signature, 120-character preview, marker presence).
• Complete list of instruments found in Bloomberg file.
• FechaCargaISO value obtained from Geneva.
• Detail of MXUDI instrument found (date and MXN/UDI price).
• Start and result of USD/MXN query in Geneva.
• USD/MXN price obtained.
• Number of final records generated (finalRows.size).
• Confirmation of write for each output file on SMB with full paths.
• Notification payload and email service response.
• Captured errors with full stacktrace.
• Specific Geneva Query errors (if the stored procedure returns ErrorMessage).

Failure Behavior

The flow uses doTry-doCatch blocks and conditional validations:

• File not found: Logs informative message and terminates without notifying (silent behavior).
• Empty file: Logs warning and terminates without notifying.
• Invalid format (ZIP/XLSX or no Bloomberg markers): Marks status as “ERROR”, notifies with specific detail and stops.
• SMB read failure: Captures exception, logs stacktrace, notifies and stops.
• FechaCargaISO not obtained: Marks error, notifies indicating JDBC query failure and stops.
• MXUDI instrument not found: Marks error, notifies including the list of instruments found in the file and stops.
• USD/MXN not obtained from Geneva: Marks error, notifies indicating Geneva returned no data and stops.
• GenevaQuery error (SQL): The stored procedure captures SQL errors and returns them in the resultset; the flow detects the presence of ErrorMessage and marks failure.
• Calculation failed (finalRows empty): Marks error, notifies and stops.

Recovery Process

• Automatic recovery: As a daily scheduled process, the next execution will attempt to process again.
• Manual recovery: If the error is due to data or connectivity:
  1. Bloomberg file: Verify availability and format of UDI_INDEX_USD.csv in SMB_IN_PATH. Validate that it contains an instrument with “MXUDI” in its code.
  2. Geneva connectivity: Verify JDBC access to ConexionGeneva and availability of stored procedures (PRC_ConectaGeneva, GenevaQuery, PRC_DesConectaGeneva).
  3. Geneva data: Verify that USD/MXN prices exist in the BL_DL_CCYALL_CLOSENY list for the calculated date.
  4. Logs: Review detailed logs to identify the specific failure stage.
  5. Manual execution: Invoke the REST endpoint of the process if reprocessing is necessary.
• No automatic retry: The flow does not implement retries; each execution is independent.
• Time dependency: The process depends on data being available in Geneva for the calculated previous business day.

Contacts

• Technical Support: Sensedia Integrations / Middleware Team.
• Data Provider: Bloomberg (source of input file).
• Target Systems: Production/Business Continuity Team.

Publishing Checklist

• Validate SMB server connectivity and access credentials from production environment.
• Verify existence and read permissions in the {{SMB_IN_PATH}} folder.
• Verify existence and write permissions in the {{SMB_OUT_PATH}} and {{SMB_OUT_PATH2}} folders.
• Confirm that UDI_INDEX_USD.csv file is deposited daily before 07:30 AM.
• Validate input file format (must contain Bloomberg markers and at least one instrument with “MXUDI”).
• Validate JDBC connectivity to ConexionGeneva from production environment.
• Verify existence and execution permissions of stored procedures in Geneva:
  • dwGeneva..PRC_ConectaGeneva
  • dwGeneva..PRC_DesConectaGeneva
  • GenevaSQL..GenevaQuery
• Validate that historical USD/MXN data exists in Geneva for the BL_DL_CCYALL_CLOSENY price list.
• Configure notification environment variables (NOTIFY_TO_EMAIL, PATRIA_NOTIFY_ENDPOINT) with production values.
• Validate that the notification endpoint is accessible and functional.
• Adjust Cron schedule if necessary according to server timezone and data availability.
• Perform end-to-end test in staging environment with real file and updated Geneva data.
• Document the date calculation logic (previous business day) for support teams.
• Validate the precision of USD/UDI calculation (12 decimals) with the business area.
• Establish monitoring alerts for consecutive failed executions.
• Document the silent behavior (no notification) when the file does not exist or is empty.

Detailed diagram of the flow in Sensedia: