Within-country Pricing

DFS transactions are typically either free, or follow two pricing structures: (1) slab-based pricing, where transactions within a set range are charged a flat fee; or (2) percentage-based pricing, where fees are set as a consistent percentage of the transaction amount.

This visualization shows how providers in the selected country price the selected transaction type. Prices for all providers in the selected country are shown in color while prices for other providers in different countries are shown in gray.

// Shared mutable state for provider types
mutable sharedProviderTypes = []

// Setup DuckDB client with data sources
dfsdb = DuckDBClient.of({
  all_prices: FileAttachment("data/all_listed_prices.csv")
})

listed_prices_raw = dfsdb.query(`
  SELECT *,
    CASE WHEN fee < 0 AND fee NOT IN (-55, -99) THEN NULL ELSE fee END as fee_clean,
    CASE WHEN fee_pct < 0 AND fee_pct NOT IN (-55, -99) THEN NULL ELSE fee_pct END as fee_pct_clean,
    CASE WHEN tax < 0 AND tax NOT IN (-55, -99) THEN NULL ELSE tax END as tax_clean,
    CASE WHEN tax_pct < 0 AND tax_pct NOT IN (-55, -99) THEN NULL ELSE tax_pct END as tax_pct_clean,
    CASE WHEN TRY_CAST(value_max AS DOUBLE) < 0 AND value_max NOT IN ('-55', '-99') THEN 10000000 ELSE TRY_CAST(value_max AS DOUBLE) END as value_max_clean,
    CASE WHEN TRY_CAST(value_min AS DOUBLE) < 0 AND value_min NOT IN ('-55', '-99') THEN 0 ELSE TRY_CAST(value_min AS DOUBLE) END as value_min_clean,
    SUBSTR(CAST(date_collection AS VARCHAR), 1, 4) || '-' || SUBSTR(CAST(date_collection AS VARCHAR), 5, 2) as month,
    CASE WHEN value_min = 'inf' OR value_min IS NULL OR TRY_CAST(value_min AS DOUBLE) IS NULL OR exchange_rate IS NULL OR exchange_rate = 0 THEN NULL ELSE TRY_CAST(value_min AS DOUBLE) / exchange_rate END as value_min_usd,
    CASE WHEN value_max = 'inf' OR value_max IS NULL OR TRY_CAST(value_max AS DOUBLE) IS NULL OR exchange_rate IS NULL OR exchange_rate = 0 THEN NULL ELSE TRY_CAST(value_max AS DOUBLE) / exchange_rate END as value_max_usd,
    UPPER(SUBSTR(country, 1, 1)) || LOWER(SUBSTR(country, 2)) as country_formatted
  FROM all_prices
`)


// Convert to plain JavaScript (bypass Arquero closure issues)
listed_prices = listed_prices_raw.map(row => ({
  // Keep all columns except the original ones we're replacing
  ...Object.fromEntries(
    Object.entries(row).filter(([key]) =>
      !['fee', 'fee_pct', 'tax', 'tax_pct', 'value_max', 'value_min', 'country'].includes(key)
    )
  ),
  // Rename cleaned columns

  fee: row.fee_clean,
  fee_pct: row.fee_pct_clean,
  fsp_type:row.fsp_type,
  tax: row.tax_clean,
  tax_pct: row.tax_pct_clean,
  value_max: row.value_max_clean,
  value_min: row.value_min_clean,
  country: row.country_formatted,
  date_collection: (() => {
    // TEMP Fix: hardcode the dates
    const betaDates = [
      20250922,
      20250923,
      20250924,
      20250925,
      20250926,
      20250929
    ];
    const expandedDates = [
      20251001,
      20251002,
      20251006,
      20251008,
      20251029
    ];
    if (betaDates.includes(row.date_collection) || expandedDates.includes(row.date_collection)) return 20251001;
    return row.date_collection; // fallback to collection_date
  })(), 
  month: (() => {
    // parse month from new date_collection
    // expects date_collection in format "YYYYMMDD"
    const betaDates = [
      20250922,
      20250923,
      20250924,
      20250925,
      20250926,
      20250929
    ];
    const expandedDates = [
      20251001,
      20251002,
      20251006,
      20251008,
      20251029
    ];
    
    let dc = row.date_collection;
    if (betaDates.includes(dc) || expandedDates.includes(dc)) {
      dc = 20251001;
    }
    
    if (dc != null) {
      const dcStr = dc.toString();
      if (dcStr.length >= 6) {
        const year = dcStr.substring(0, 4);
        const month = dcStr.substring(4, 6);
        return `${year}-${month}`;
      }
    }
    return row.month;
  })()
}
))

fixProviderTypeCapitalization = (providerType) => {
  const fixes = {
    'mobile banking': 'Mobile banking',
    'mobile money': 'Mobile money',
    'other types of fsps': 'Other types of FSPs'
  };
  return fixes[providerType] || providerType;
};

// Generate transaction values from 1 to 100 USD
transaction_values_usd = Array.from({length: 100}, (_, i) => ({ value_usd: i + 1 }))

// Function to calculate fee percentage for each provider's fee structure
function calculateFees(data, transactionValues) {
  const allFees = [];

  // Use data directly as it's already plain JavaScript objects
  const dataObjects = data;
  const invalid_values = [-99, -66, -88, -55]
  for (const row of dataObjects) {
    // Skip rows with -55 or -99 or -66 or -88 values in fee, fee_pct, value_min, or value_max
    if ((row.fee !== null && (row.fee === -55 || row.fee === -99 || row.fee === -88 || row.fee === -66)) ||
        (row.fee_pct !== null && (row.fee_pct === -55 || row.fee_pct === -99 || row.fee_pct === -88 || row.fee_pct === -66)) ||
        (row.value_min !== null && (row.value_min === -55 || row.value_min === -99 || row.value_min === -88 || row.value_min === -66)) ||
        (row.value_max !== null && (row.value_max === -55 || row.value_max === -99 || row.value_max === -88 || row.value_max === -66))) {
      continue;
    }

    // Handle missing or invalid value_min and value_max
    const valueMinUsd = (row.value_min == null || row.value_min < 0) ? 0 : row.value_min / row.exchange_rate;
    const valueMaxUsd = (row.value_max == null || row.value_max < 0) ? Infinity : row.value_max / row.exchange_rate;

    // Calculate which transaction values fall within this fee structure's range
    const applicableValues = transactionValues.filter(tv =>
      tv.value_usd >= valueMinUsd && tv.value_usd <= valueMaxUsd
    );


    if (applicableValues.length > 0) {
      // For each applicable value, calculate fee
      for (const tv of applicableValues) {
        const localCurrency = tv.value_usd * row.exchange_rate;
        const fixedFee = row.fee || 0;
        const pctFee = row.fee_pct || 0;
        const tax = row.tax || 0;
        const taxPct = row.tax_pct || 0;
        const taxType = row.tax_type;

        // Calculate total fee depending on tax_type
        let totalFee;
        if (taxType === 1 && !invalid_values.includes(tax) && !invalid_values.includes(taxPct)) {
          totalFee = fixedFee + (pctFee / 100 * localCurrency) + tax + (taxPct / 100 * localCurrency);
        } else {
          totalFee = fixedFee + (pctFee / 100 * localCurrency);
        }

        // Calculate as percentage
        const feePercentage = (totalFee / localCurrency) * 100;

        allFees.push({
          value_usd: tv.value_usd,
          provider: row.provider,
          fsp_type: row.fsp_type,
          transaction_type: row.transaction_type,
          country: row.country,
          month: row.month,
          local_currency: localCurrency,
          fixed_fee: fixedFee,
          pct_fee: pctFee,
          tax: tax,
          tax_pct: taxPct,
          tax_type: taxType,
          total_fee: totalFee,
          fee_percentage: feePercentage
        });
      }
    }

  }

  return allFees;
}


// Calculate fees for all providers and transaction types
fee_data_raw = calculateFees(listed_prices, transaction_values_usd)

// Format transaction type names to be more readable using plain JavaScript
fee_data = fee_data_raw.map(d => {
  let transaction_type_formatted;
  switch(d.transaction_type) {
    case "cash-in via agent": transaction_type_formatted = "Cash-in via agent"; break;
    case "cash-in via atm": transaction_type_formatted = "Cash-in via ATM"; break;
    case "p2p on-network transfer": transaction_type_formatted = "On-net transfer"; break;
    case "p2p off-network transfer": transaction_type_formatted = "Off-net transfer"; break;
    case "p2p to unregistered user": transaction_type_formatted = "Transfer to unregistered user"; break;
    case "cash-out via agent": transaction_type_formatted = "Cash-out via agent"; break;
    case "cash-out via atm": transaction_type_formatted = "Cash-out via ATM"; break;
    case "wallet to bank": transaction_type_formatted = "Wallet-to-bank transfer"; break;
    case "bank to wallet": transaction_type_formatted = "Bank-to-wallet transfer"; break;
    case "payment at merchant": transaction_type_formatted = "Merchant payment"; break;
    case "utility payment": transaction_type_formatted = "Utility payment"; break;
    default: transaction_type_formatted = d.transaction_type; break;
  }

  // Return object without original transaction_type, renamed to transaction_type
  d.transaction_type = transaction_type_formatted;
  return d;
})

import {prettyName} from "/assets/js/utilities.js"
import {primaryPalette as palette} from "/assets/js/colors.js"

// Use data directly from Observable processing
pricesData = listed_prices
feeData = fee_data


//| panel: input
// Extract category values from the data and sort in reverse chronological order
uniqueCountry = [...new Set(feeData.map(prices => prices.country))].sort()

//| panel: input
viewof selectedCountry = Inputs.select(
  uniqueCountry,
  { value: "Bangladesh",
    label: "Country:",
    multiple: false,
    sort: true,
    unique: true
  }
)

// Extract unique month values from the data and sort in reverse chronological order
uniqueMonthRaw = [...new Set(feeData.map(prices => prices.month))].sort().reverse();
uniqueMonthDisplay = uniqueMonthRaw.map(m => {
  const [year, month] = m.split('-');
  const monthNames = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
  return `${year}-${monthNames[parseInt(month, 10) - 1]}`;
});

// Map display month to raw value
monthMap = Object.fromEntries(uniqueMonthDisplay.map((disp, i) => [disp, uniqueMonthRaw[i]]));

viewof selectedMonth = Inputs.select(
  uniqueMonthDisplay,
  { value: uniqueMonthDisplay[0],
    label: "Month:",
    multiple: false,
    sort: false, // Disable sorting to preserve reverse chronological order
    unique: true
  }
)

// Define the desired order for transaction types
orderedCategories = [
  "Cash-in via agent",
  "Cash-in via ATM",
  "Cash-out via agent",
  "Cash-out via ATM",
  "On-net transfer",
  "Off-net transfer",
  "Transfer to unregistered user",
  "Utility payment",
  "Merchant payment",
  "Wallet-to-bank transfer",
  "Bank-to-wallet transfer"
]

// Get all available transaction types
availableCategories = [...new Set(feeData.map(d => d.transaction_type))]

// Filter orderedCategories to include only types that exist in the data
uniqueCategory = orderedCategories.filter(type => availableCategories.includes(type))

// Get unique provider types from the data with fixed capitalization
uniqueProviderTypes = [...new Set(feeData.map(d => fixProviderTypeCapitalization(d.fsp_type)))].filter(d => d != null).sort()



// Initialize provider types if needed
{
  if (sharedProviderTypes.length === 0 && uniqueProviderTypes.length > 0) {
    mutable sharedProviderTypes = uniqueProviderTypes;
  }
  return html``;
}

viewof selectedTransactionType = Inputs.select(
  uniqueCategory,
  { value: "Cash-out via agent",
    label: "Transaction Types:",
    multiple: false,
    sort: false,
    unique: true
  }
)

//| panel: input
viewof selectedProviderTypes = Inputs.checkbox(
  uniqueProviderTypes,
  {
    value: sharedProviderTypes,
    label: "Provider Types:",
    multiple: true,
    sort: true,
    unique: true
  }
)

filteredData = feeData.filter(d =>
  d.month === monthMap[selectedMonth] &&
  d.transaction_type === selectedTransactionType &&
  selectedProviderTypes && selectedProviderTypes.length > 0 && 
  d.fsp_type && selectedProviderTypes.includes(fixProviderTypeCapitalization(d.fsp_type))
)
// Create a unique ID for each provider+country combination to ensure we draw one line per combination
filteredData.forEach(d => {
  d.lineId = d.provider + "_" + d.country;

  // Add display name for tooltips - include provider name for all countries
  if (filteredData.filter(item => item.country === d.country).length > 1) {
    // Multiple providers for this country
    d.displayName = d.provider + " (" + d.country + ")";
  } else {
    // Single provider for this country
    d.displayName = d.country;
  }

  // Add tooltipPriority to ensure selected country takes precedence in overlapping cases
  d.tooltipPriority = d.country === selectedCountry ? 1 : 0;
})

sortedByValue = filteredData.sort((a, b) => a.value_usd - b.value_usd);

// For rendering, specifically in case of overlaps, we want to prepare our data in two sets
// First, we'll have all non-selected countries, then selected country on top
// This way, the tooltips for selected country will take precedence when there's overlap
plotData = [
  ...sortedByValue.filter(d => d.country !== selectedCountry),
  ...sortedByValue.filter(d => d.country === selectedCountry)
]

Plot

hasSelectedCountryData = plotData.some(d => d.country === selectedCountry)

// First get unique countries from the raw data
dataCountries = [...new Set(pricesData.map(d => d.country))]

// Get countries with plotted data - these have entries in plotData
countriesWithPlottedData = [...new Set(plotData.map(d => d.country))]

// Map formatted transaction type back to original CSV transaction type
rawTransactionType = (() => {
  switch(selectedTransactionType) {
    case "Cash-in via agent": return "cash-in via agent";
    case "Cash-in via ATM": return "cash-in via atm";
    case "On-net transfer": return "p2p on-network transfer";
    case "Off-net transfer": return "p2p off-network transfer";
    case "Transfer to unregistered user": return "p2p to unregistered user";
    case "Cash-out via agent": return "cash-out via agent";
    case "Cash-out via ATM": return "cash-out via atm";
    case "Wallet-to-bank transfer": return "wallet to bank";
    case "Bank-to-wallet transfer": return "bank to wallet";
    case "Merchant payment": return "payment at merchant";
    case "Utility payment": return "utility payment";
    default: return selectedTransactionType.toLowerCase();
  }
})()

// Identify countries with technical issues (-55 values) not already in plotData
countriesWithTechnicalIssues = [...new Set(pricesData
  .filter(d =>
    d.transaction_type.toLowerCase() === rawTransactionType.toLowerCase() &&
    d.month === monthMap[selectedMonth] &&
    (d.fee == -55 || d.fee_pct == -55) && // Using == instead of === for numeric comparison
    !countriesWithPlottedData.includes(d.country) // Make sure this country doesn't already have plotted data
  )
  .map(d => d.country)
)]

// Get countries that are in the dataset but have -99 fees (not listed)
// These are countries with "not listed" fees
countriesWithNoListedFees = [...new Set(pricesData
  .filter(d =>
    d.transaction_type.toLowerCase() === rawTransactionType.toLowerCase() &&
    d.month === monthMap[selectedMonth] &&
    (d.fee == -99 || d.fee_pct == -99)
  )
  .map(d => d.country)
)]

// Create combined footnote text with both notes and country listings
footnoteText = html`
<div style="margin-top: 15px; font-size: 14px; max-width: 800px;">
  <p><strong>Notes:</strong></p>
  <ol style="padding-left: 20px; margin-top: 5px;">
    <li>
      ${countriesWithNoListedFees.length > 0 ? html`<span style="font-style: italic;">Countries with unlisted fees for ${selectedTransactionType}: ${countriesWithNoListedFees.join(", ")}</span><br/>` : ''}
      Fees labeled "not listed" are not clearly available on provider websites. This may indicate the transaction is not offered by the provider, or the transaction is offered but lacks clear pricing on the website. In these cases, prices may be missing, ambiguous, inconsistent, or described only as "varies."
    </li>
    <li>
      ${countriesWithTechnicalIssues.length > 0 ? html`<span style="font-style: italic;">Countries with unavailable fees for ${selectedTransactionType} due to technical issues: ${countriesWithTechnicalIssues.join(", ")}</span><br/>` : ''}
      Fees labeled as "unavailable due to technical issues" are available on provider websites, but IPA was unable to collect this information due to technical difficulties with our data collection process.
    </li>
    <li>
    The beta version of data collection, limited to a subset of providers and countries, ran through September 2025. In October 2025, coverage expanded to include all providers and countries. Differences in reported fees between those months are therefore primarily due to this rollout, not real-world price changes.</li>
  </ol>
</div>`

// Empty placeholders for sections that will no longer be used
technicalIssuesSection = html``;
notListedSection = html``;

// Get providers for the selected country that actually have plotted data (not -99 or filtered out)
// Only include providers that have valid fee_percentage data points
selectedCountryProviders = [...new Set(plotData
  .filter(d => 
    d.country === selectedCountry && 
    d.fee_percentage != null && 
    !isNaN(d.fee_percentage) && 
    d.fee_percentage >= 0
  )
  .map(d => d.provider))].sort()
selectedCountryProvidersB = [...new Set(plotData
  .filter(d => 
    d.country === selectedCountry && 
    d.fee_percentage != null && 
    !isNaN(d.fee_percentage) && 
    d.fee_percentage >= 0
  )
)];
// Create a special color scheme for the selected country's providers
hasMultipleProvidersForSelectedCountry = selectedCountryProviders.length > 1;

// Import primary color palette and create provider-specific palette
import { primaryPalette } from "./assets/js/colors.js";

// Create a color palette that adapts to the number of providers
providerColorPalette = primaryPalette.slice(0, Math.max(selectedCountryProviders.length, 2));

// Create a color mapping function that handles multiple providers in selected country
function getStrokeColor(d) {
  // If not the selected country, always gray
  if (d.country !== selectedCountry) return "#c9c9c8";

  // If only one provider in selected country, use orange
  if (!hasMultipleProvidersForSelectedCountry) return "#f26529";

  // Otherwise, return a provider-specific color from palette
  const providerIndex = selectedCountryProviders.indexOf(d.provider);
  if (providerIndex === -1) return "#c9c9c8"; // Fallback for unexpected cases
  return providerColorPalette[providerIndex % providerColorPalette.length];
}

// Create a legend specifically for providers in the selected country
providerLegendItems = selectedCountryProviders.map((provider, i) => {
  // Find the provider type for this provider in the current data
  const providerData = plotData.find(d => d.provider === provider && d.country === selectedCountry);
  const providerType = providerData ? fixProviderTypeCapitalization(providerData.fsp_type) : '';
  
  // Use the same color logic as getStrokeColor function
  let color;
  if (!hasMultipleProvidersForSelectedCountry) {
    color = "#f26529"; // Orange for single provider
  } else {
    color = providerColorPalette[i % providerColorPalette.length]; // Palette colors for multiple providers
  }
  
  return {
    color: color,
    label: providerType ? `${provider} (${providerType})` : provider
  };
});

// Always create the legend HTML for providers
providerLegend = selectedCountryProviders.length > 0 ?
  html`<div style="margin-top: 10px; margin-bottom: 10px;">
    <strong>Providers in ${selectedCountry}:</strong>
    <div style="display: flex; gap: 15px; margin-top: 5px;">
      ${providerLegendItems.map(item => html`
        <div style="display: flex; align-items: center;">
          <div style="width: 12px; height: 12px; background-color: ${item.color}; margin-right: 5px;"></div>
          <span>${item.label}</span>
        </div>
      `)}
    </div>
  </div>` :
  html``;

html`
<div>
${Plot.plot({
  title: `${prettyName(selectedTransactionType)} Pricing in ${prettyName(selectedCountry)} (${selectedMonth})`,
  width: 800,
  height: 500,
  x: {
    label: "Transaction Value (USD)",
    domain: [0,100]
  },
  y: {
    label: "Fee (% of Transaction Value)",
    nice: true,
    grid: true,
    tickFormat: d => d + "%"
  },
  color: {
    legend: false
  },
  marks: [
      Plot.ruleY([0]),
      // Draw lines for non-selected countries (gray, no tooltip)
      Plot.line(
        plotData.filter(d => d.country !== selectedCountry),
        {
          x: "value_usd",
          y: "fee_percentage",
          stroke: "#c9c9c8",
          strokeWidth: 2,
          opacity: 0.4,
          curve: "linear",
          z: d => `${d.country}_${d.provider}`,
          sort: {y: "x"},
          tip: false // Disable tooltip for gray lines
        }
      ),
      // Draw lines for selected country (colored, with tooltip)
      Plot.line(
        plotData.filter(d => d.country === selectedCountry),
        {
          x: d => {
            // Add small X-axis jitter for overlapping providers
            if (hasMultipleProvidersForSelectedCountry) {
              const providerIndex = selectedCountryProviders.indexOf(d.provider);
              const jitterAmount = 0.3; // Small X-axis jitter
              const jitter = (providerIndex - (selectedCountryProviders.length - 1) / 2) * jitterAmount;
              return d.value_usd + jitter;
            }
            return d.value_usd;
          },
          y: d => {
            // Add small Y-axis jitter for overlapping providers
            if (hasMultipleProvidersForSelectedCountry) {
              const providerIndex = selectedCountryProviders.indexOf(d.provider);
              const jitterAmount = 0.35; // Reduced Y-axis jitter
              const jitter = (providerIndex - (selectedCountryProviders.length - 1) / 2) * jitterAmount;
              return Math.max(0, d.fee_percentage + jitter); // Ensure y-value never goes below 0
            }
            return d.fee_percentage;
          },
          stroke: d => getStrokeColor(d),
          strokeWidth: 3,
          opacity: 1,
          curve: "linear",
          z: d => `${d.country}_${d.provider}`,
          sort: {y: "x"},
          channels: {
            "Country": "country",
            "Provider": "provider",
            "Fee (%)": d => d.fee_percentage != null ? d.fee_percentage.toFixed(2) + "%" : "N/A"
          },
          tip: {
            format: {
              y: false,
              x: false,
              stroke: false,
              opacity: false,
              z: false,
              strokeWidth: false
            }
          }
        }
      ),
  ]
})}
${providerLegend}
${footnoteText}
</div>`

Data

Inputs.table(plotData, {select: false})

// from https://observablehq.com/@jeremiak/download-data-button
button = (data, filename = 'data.csv') => {
  if (!data) throw new Error('Array of data required as first argument');

  let downloadData;
  if (filename.includes('.csv')) {
    downloadData = new Blob([d3.csvFormat(data)], { type: "text/csv" });
  } else {
    downloadData = new Blob([JSON.stringify(data, null, 2)], {
      type: "application/json"
    });
  }

  const size = (downloadData.size / 1024).toFixed(0);
  const button = DOM.download(
    downloadData,
    filename,
    `Download Data (~${size} KB)`
  );
  return button;
}