Currency Rate Documentation

Overview

The Currency Rate module (currency.rate) stores historical exchange rate data for all currencies in the system. Each record represents the buy and sell rates for a specific currency on a specific date, enabling accurate multi-currency transactions and historical reporting.

---

Model Information

Model Name: currency.rate Display Name: Currency Rate Key Fields: None (allows multiple rates per currency) Default Sort Order: date desc, id desc (newest first)

Features

• ✅ Multi-company support
• ✅ Historical rate tracking by date
• ✅ Buy and sell rate separation
• ✅ IPC cache synchronization
• ✅ Cascade delete with parent currency
• ✅ Default date (today)
• ✅ 6 decimal places precision for rates

---

Understanding Currency Rates

What are Currency Rates?

Currency rates define the exchange value between currencies at specific points in time. Each rate record contains:

1. Date - When this rate is effective
2. Buy Rate - Rate for buying the foreign currency (payments out)
3. Sell Rate - Rate for selling the foreign currency (receipts in)
4. Company - Which company this rate applies to (optional)

Buy vs. Sell Rates

Buy Rate: - Used when you purchase foreign currency - Applied to: Supplier payments, purchases, expenses in foreign currency - Typically lower (less favorable) for the business

Sell Rate: - Used when you sell foreign currency - Applied to: Customer receipts, sales, income in foreign currency - Typically higher (more favorable) for the business

Example:

Date: 2025-01-15
Currency: USD

Buy Rate:  35.50 THB/USD  (You pay 35.50 THB to get 1 USD)
Sell Rate: 36.00 THB/USD  (You receive 36.00 THB for 1 USD)
Spread:    0.50 THB       (Bank/exchange profit)

Rate History

The system maintains complete rate history: - Multiple rate records per currency - Sorted by date (newest first) - Historical transactions use rate from transaction date - Missing rates fall back to nearest previous rate

Multi-Company Rates

Companies can have: - Company-specific rates - Custom rates for one company - Global rates - Shared across all companies (company_id = NULL) - System searches company-specific first, then falls back to global

---

Key Fields Reference

Core Fields

Field	Type	Required	Description
currency_id	Many2One	✅	Link to currency record (cascading delete)
date	Date	✅	Effective date for this rate (default: today)
sell_rate	Decimal(6)	✅	Selling rate (receiving foreign currency)
buy_rate	Decimal(6)	✅	Buying rate (paying foreign currency)
company_id	Many2One	❌	Company this rate applies to (NULL = global)

Deprecated Fields

Field	Type	Description
rate	Decimal(6)	Deprecated - use buy_rate or sell_rate instead

Field Details

currency_id: - Required reference to parent currency - Cascade delete: Deleting currency removes all its rates - Each currency can have unlimited rate records

date: - Format: "YYYY-MM-DD" - Defaults to current date - Used for historical rate lookups - Can have multiple rates per date (for different companies)

sell_rate & buy_rate: - 6 decimal places precision (e.g., 35.123456) - Both required fields - Must be positive values - Expressed as: 1 unit of this currency = X base currency units

company_id: - Optional - NULL means rate applies to all companies - Defaults to active company - Company-specific rates take precedence over global rates

---

API Methods

1. Create Rate

Method: create(vals, context)

Creates a new exchange rate record and clears the currency cache.

Parameters:

vals = {
    "currency_id": currency_id,    # Required
    "date": "2025-01-15",          # Optional (defaults to today)
    "buy_rate": 35.50,             # Required
    "sell_rate": 36.00,            # Required
    "company_id": company_id       # Optional (defaults to active)
}

Returns: int - New rate record ID

Example:

# Create global rate (all companies)
rate_id = get_model("currency.rate").create({
    "currency_id": usd_id,
    "date": "2025-01-15",
    "buy_rate": 35.50,
    "sell_rate": 36.00,
    "company_id": None  # Global rate
})

# Create company-specific rate
rate_id = get_model("currency.rate").create({
    "currency_id": eur_id,
    "date": "2025-01-15",
    "buy_rate": 38.50,
    "sell_rate": 39.00,
    "company_id": company_id
})

Behavior: - Sends IPC signal to clear currency cache - Defaults date to today if not provided - Defaults company_id to active company if not provided - No validation prevents duplicate dates (allows multiple rates per date)

---

2. Update Rate

Method: write(ids, vals, context)

Updates existing rate record(s) and clears the cache.

Parameters: - ids (list): Rate record IDs to update - vals (dict): Fields to update - context (dict): Optional context

Example:

# Update rate values
get_model("currency.rate").write([rate_id], {
    "buy_rate": 35.75,
    "sell_rate": 36.25
})

# Change effective date
get_model("currency.rate").write([rate_id], {
    "date": "2025-01-16"
})

Behavior: - Clears currency cache via IPC signal - Can update any field - Multiple IDs can be updated in one call

---

3. Delete Rate

Method: delete(ids, context)

Deletes rate record(s) and clears the cache.

Parameters: - ids (list): Rate record IDs to delete - context (dict): Optional context

Example:

# Delete old rates
old_rates = get_model("currency.rate").search([
    ["date", "<", "2020-01-01"]
])
get_model("currency.rate").delete(old_rates)

Behavior: - Sends IPC signal to clear currency cache - Permanently removes rate records - No cascade effects (rates are leaf records)

---

Search Functions

Find Current Rate for Currency

# Get most recent rate for USD
rates = get_model("currency.rate").search_browse([
    ["currency_id.code", "=", "USD"]
], order="date desc,id desc", limit=1)

if rates:
    latest = rates[0]
    print(f"USD Rate on {latest.date}")
    print(f"  Buy:  {latest.buy_rate}")
    print(f"  Sell: {latest.sell_rate}")

Find Rate for Specific Date

# Get rate for specific date or nearest previous
target_date = "2025-01-15"

rates = get_model("currency.rate").search_browse([
    ["currency_id", "=", usd_id],
    ["date", "<=", target_date]
], order="date desc,id desc", limit=1)

Find Company-Specific Rate

# Get rate for specific company
rates = get_model("currency.rate").search_browse([
    ["currency_id", "=", eur_id],
    ["company_id", "=", company_id],
    ["date", "=", "2025-01-15"]
])

Find All Rates in Date Range

# Get rate history for date range
rates = get_model("currency.rate").search_browse([
    ["currency_id", "=", usd_id],
    ["date", ">=", "2025-01-01"],
    ["date", "<=", "2025-01-31"]
], order="date desc")

print("USD Rate History - January 2025")
for rate in rates:
    print(f"{rate.date}: Buy {rate.buy_rate}, Sell {rate.sell_rate}")

Find Currencies Without Rates

# Find currencies that need rates
currencies = get_model("currency").search_browse([
    ["rates", "=", None]
])

print("Currencies without exchange rates:")
for curr in currencies:
    print(f"  - {curr.code}: {curr.name}")

---

Best Practices

1. Maintain Daily Rate Updates

# Good: Update rates daily
from datetime import date

today = str(date.today())

# Check if today's rate exists
existing = get_model("currency.rate").search([
    ["currency_id", "=", usd_id],
    ["date", "=", today]
])

if not existing:
    # Create today's rate
    get_model("currency.rate").create({
        "currency_id": usd_id,
        "date": today,
        "buy_rate": 35.50,
        "sell_rate": 36.00
    })

---

2. Use Appropriate Rate Precision

# Good: Use 6 decimal places for accuracy
rate_id = get_model("currency.rate").create({
    "currency_id": jpy_id,
    "date": "2025-01-15",
    "buy_rate": 0.262150,   # Precise rate
    "sell_rate": 0.263500
})

# Acceptable: Fewer decimals if appropriate
rate_id = get_model("currency.rate").create({
    "currency_id": usd_id,
    "date": "2025-01-15",
    "buy_rate": 35.50,      # Common precision
    "sell_rate": 36.00
})

---

3. Consider Rate Spread

# Good: Realistic spread between buy and sell
rate_id = get_model("currency.rate").create({
    "currency_id": usd_id,
    "date": "2025-01-15",
    "buy_rate": 35.50,
    "sell_rate": 36.00  # 0.50 spread (1.4%)
})

# Bad: Same buy and sell (unrealistic)
rate_id = get_model("currency.rate").create({
    "currency_id": usd_id,
    "date": "2025-01-15",
    "buy_rate": 35.50,
    "sell_rate": 35.50  # No spread
})

---

4. Archive Old Rates Carefully

# Consider before deleting old rates
# They're needed for historical reporting and audit

# Bad: Deleting all old rates
old_rates = get_model("currency.rate").search([
    ["date", "<", "2020-01-01"]
])
get_model("currency.rate").delete(old_rates)  # May break history

# Good: Keep rates or archive to separate table
# Only delete if absolutely certain no historical data references them

---

Database Constraints

Foreign Key Constraints

-- Currency reference with cascade delete
FOREIGN KEY (currency_id)
    REFERENCES currency(id)
    ON DELETE CASCADE

-- Company reference
FOREIGN KEY (company_id)
    REFERENCES company(id)

Implications: - Deleting currency deletes all its rates automatically - Deleting company may affect rate records (depending on DB config) - No unique constraint on (currency_id, date, company_id) - duplicates allowed

---

Related Models

Model	Relationship	Description
currency	Many2One	Parent currency for these rates
company	Many2One	Company these rates apply to (optional)
account.invoice	Indirect	Uses rates for currency conversion
account.payment	Indirect	Uses rates for currency conversion
account.move.line	Indirect	Stores converted amounts using these rates

---

Common Use Cases

Use Case 1: Daily Rate Update from External API

# Automated daily job to fetch and update rates

import requests
from datetime import date

def update_daily_rates():
    today = str(date.today())

    # Fetch rates from external API (example)
    response = requests.get("https://api.exchangerate.com/latest")
    api_rates = response.json()["rates"]

    # Base currency is THB (rate = 1.0)
    base_currency_id = get_model("currency").search([
        ["code", "=", "THB"]
    ])[0]

    # Update each currency
    for curr_code, rate_value in api_rates.items():
        # Find currency
        curr_ids = get_model("currency").search([
            ["code", "=", curr_code]
        ])
        if not curr_ids:
            continue

        currency_id = curr_ids[0]

        # Check if rate exists for today
        existing = get_model("currency.rate").search([
            ["currency_id", "=", currency_id],
            ["date", "=", today],
            ["company_id", "=", None]  # Global rate
        ])

        # Calculate buy/sell with 1% spread
        buy_rate = rate_value * 0.995
        sell_rate = rate_value * 1.005

        if existing:
            # Update existing
            get_model("currency.rate").write(existing, {
                "buy_rate": buy_rate,
                "sell_rate": sell_rate
            })
        else:
            # Create new
            get_model("currency.rate").create({
                "currency_id": currency_id,
                "date": today,
                "buy_rate": buy_rate,
                "sell_rate": sell_rate,
                "company_id": None  # Global
            })

        print(f"✓ Updated {curr_code}: {buy_rate:.6f} / {sell_rate:.6f}")

# Run daily
update_daily_rates()

---

Use Case 2: Import Historical Rates from CSV

# Import historical exchange rates from CSV file

import csv

def import_historical_rates(csv_file):
    with open(csv_file, 'r') as f:
        reader = csv.DictReader(f)

        for row in reader:
            # Get currency
            currency_id = get_model("currency").search([
                ["code", "=", row["currency_code"]]
            ])[0]

            # Check if rate already exists
            existing = get_model("currency.rate").search([
                ["currency_id", "=", currency_id],
                ["date", "=", row["date"]],
                ["company_id", "=", None]
            ])

            if existing:
                print(f"⊘ Skipping {row['currency_code']} on {row['date']} (exists)")
                continue

            # Create rate
            get_model("currency.rate").create({
                "currency_id": currency_id,
                "date": row["date"],
                "buy_rate": float(row["buy_rate"]),
                "sell_rate": float(row["sell_rate"]),
                "company_id": None
            })

            print(f"✓ Imported {row['currency_code']} on {row['date']}")

# CSV format: currency_code,date,buy_rate,sell_rate
# USD,2025-01-01,35.00,35.50
# USD,2025-01-02,35.10,35.60
# EUR,2025-01-01,38.00,38.50

import_historical_rates("historical_rates.csv")

---

Use Case 3: Company-Specific Rate Override

# Set special rates for specific company (e.g., negotiated rates)

company_id = 5  # Subsidiary company
date = "2025-01-15"

# Create company-specific rate (overrides global rate)
get_model("currency.rate").create({
    "currency_id": usd_id,
    "date": date,
    "buy_rate": 35.00,   # Better than global 35.50
    "sell_rate": 36.50,  # Worse than global 36.00
    "company_id": company_id
})

# When this company converts USD, it uses these rates instead of global

---

Use Case 4: Rate History Report

# Generate rate change report for analysis

currency_code = "USD"
from_date = "2025-01-01"
to_date = "2025-01-31"

# Get currency
currency_id = get_model("currency").search([["code", "=", currency_code]])[0]

# Get all rates in range
rates = get_model("currency.rate").search_browse([
    ["currency_id", "=", currency_id],
    ["date", ">=", from_date],
    ["date", "<=", to_date]
], order="date asc")

print(f"EXCHANGE RATE HISTORY - {currency_code}")
print(f"Period: {from_date} to {to_date}")
print("=" * 80)
print(f"{'Date':12} {'Buy Rate':>12} {'Sell Rate':>12} {'Change %':>12}")
print("-" * 80)

prev_buy = None
for rate in rates:
    if prev_buy:
        change = ((rate.buy_rate - prev_buy) / prev_buy) * 100
        change_str = f"{change:+.2f}%"
    else:
        change_str = "-"

    print(f"{rate.date:12} {rate.buy_rate:>12.6f} {rate.sell_rate:>12.6f} {change_str:>12}")
    prev_buy = rate.buy_rate

---

Use Case 5: Cleanup Duplicate Rates

# Find and remove duplicate rates (same currency, date, company)

def cleanup_duplicate_rates():
    # Get all rates
    all_rates = get_model("currency.rate").search_browse([])

    # Group by currency, date, company
    seen = {}
    duplicates = []

    for rate in all_rates:
        key = (rate.currency_id.id, rate.date, rate.company_id.id if rate.company_id else None)

        if key in seen:
            # Duplicate found - keep the newer one (higher ID)
            if rate.id > seen[key].id:
                duplicates.append(seen[key].id)
                seen[key] = rate
            else:
                duplicates.append(rate.id)
        else:
            seen[key] = rate

    if duplicates:
        print(f"Found {len(duplicates)} duplicate rates")
        print("Deleting duplicates...")
        get_model("currency.rate").delete(duplicates)
        print("✓ Cleanup complete")
    else:
        print("✓ No duplicate rates found")

cleanup_duplicate_rates()

---

Performance Tips

1. Index on Common Search Fields

-- Recommended indexes for currency.rate table
CREATE INDEX idx_currency_rate_currency ON currency_rate(currency_id);
CREATE INDEX idx_currency_rate_date ON currency_rate(date);
CREATE INDEX idx_currency_rate_company ON currency_rate(company_id);
CREATE INDEX idx_currency_rate_lookup
    ON currency_rate(currency_id, date DESC, company_id);

2. Limit Historical Queries

# Good: Limit results when you only need latest
rate = get_model("currency.rate").search_browse([
    ["currency_id", "=", usd_id]
], order="date desc", limit=1)

# Less efficient: Loading all rates
all_rates = get_model("currency.rate").search_browse([
    ["currency_id", "=", usd_id]
])
latest = all_rates[0]  # Only needed first one

3. Cache Results in Application

# For frequently accessed rates within a session
rate_cache = {}

def get_cached_rate(currency_id, date):
    key = (currency_id, date)
    if key not in rate_cache:
        rates = get_model("currency.rate").search_browse([
            ["currency_id", "=", currency_id],
            ["date", "<=", date]
        ], order="date desc", limit=1)
        rate_cache[key] = rates[0] if rates else None
    return rate_cache[key]

---

Troubleshooting

"No exchange rate found for date"

Cause: No rate record exists on or before specified date Solution: Create rate for that date or earlier:

get_model("currency.rate").create({
    "currency_id": currency_id,
    "date": "2025-01-01",  # Start of period
    "buy_rate": 35.50,
    "sell_rate": 36.00
})

"Wrong rate being used"

Cause 1: Company-specific rate overriding expected global rate Solution: Check for company-specific rates:

rates = get_model("currency.rate").search_browse([
    ["currency_id", "=", usd_id],
    ["date", "=", "2025-01-15"]
])
for rate in rates:
    company = rate.company_id.name if rate.company_id else "Global"
    print(f"{company}: Buy {rate.buy_rate}, Sell {rate.sell_rate}")

Cause 2: Using wrong rate type (buy vs. sell) Solution: Verify rate_type parameter in conversion

"Rates not updating in real-time"

Cause: Currency cache not cleared or stale Solution: Cache is automatically cleared on create/write/delete. If issue persists, check IPC configuration:

from netforce import ipc
ipc.send_signal("clear_currency_cache")

"Duplicate rates causing confusion"

Cause: Multiple rates for same currency/date combination Solution: Remove duplicates (see Use Case 5) or distinguish by company_id

---

Testing Examples

Unit Test: Rate Creation and Retrieval

def test_currency_rate():
    # Create test currency
    curr_id = get_model("currency").create({
        "name": "Test Dollar",
        "code": "TUSD",
        "sign": "$"
    })

    # Create rate
    rate_id = get_model("currency.rate").create({
        "currency_id": curr_id,
        "date": "2025-01-15",
        "buy_rate": 35.50,
        "sell_rate": 36.00
    })

    # Retrieve and verify
    rate = get_model("currency.rate").browse([rate_id])[0]
    assert rate.currency_id.id == curr_id
    assert rate.date == "2025-01-15"
    assert rate.buy_rate == 35.50
    assert rate.sell_rate == 36.00

    # Test currency current rate
    currency = get_model("currency").browse([curr_id])[0]
    assert currency.buy_rate == 35.50
    assert currency.sell_rate == 36.00

    print("✓ Currency rate test passed")

    # Cleanup
    get_model("currency").delete([curr_id])

---

Security Considerations

Permission Model

• Rate creation/modification requires accounting or admin permissions
• Daily rate updates may use service account
• Rate changes are tracked via IPC signals (consider audit logging)

Data Access

• Rates can be global or company-specific
• Multi-company setups should verify rate access controls
• Consider restricting who can create company-specific rates

Data Integrity

• No unique constraint allows duplicates (intentional for multi-company)
• Application logic should handle duplicate rates appropriately
• Consider validation rules for rate reasonableness

---

Integration Points

Internal Modules

• currency: Parent currency model
• company: Multi-company rate support
• account.invoice: Uses rates for foreign currency invoices
• account.payment: Uses rates for foreign currency payments
• account.move.line: Stores converted amounts

External Systems

• Exchange rate APIs (automated updates)
• Banking systems (rate imports)
• Treasury management systems

---

Version History

Last Updated: 2025-12-16 Model Version: currency_rate.py Framework: Netforce

---

Additional Resources

• Currency Documentation: currency
• Multi-Currency Accounting Guide
• Exchange Rate API Integration Guide
• Company Model Documentation: company

---

Support & Feedback

For issues or questions about currency rates: 1. Verify rate exists for required date 2. Check company-specific vs. global rates 3. Review rate type (buy vs. sell) usage 4. Ensure rates are updated regularly

---

This documentation is generated for developer onboarding and reference purposes.