Skip to content

Instantly share code, notes, and snippets.

@jtbr

jtbr/balance.md

Last active June 16, 2026 10:58
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save jtbr/80bb325d16a1fb73f17aa4b85bd3b534 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save jtbr/80bb325d16a1fb73f17aa4b85bd3b534 to your computer and use it in GitHub Desktop.
Download ZIP
Bank Balance Viewer
Raw
balance.md

Bank Balance Viewer

A self-contained, offline tool for tracking account balances across banks and currencies

What it does

Bank Balance Viewer reads CSV exports from your bank accounts and plots your balances over time, across multiple accounts and currencies. You can view accounts individually or summed together. Exchange rates are fetched automatically from the European Central Bank so balances in different currencies can be compared on a single chart.

Everything runs locally in your browser. No data is uploaded or shared with any server.

How to use it

  1. Download the file and open it in your browser (Chrome, Firefox, Edge, or Safari).
  2. Export a CSV from your bank's online portal or app.
  3. Drag and drop the CSV onto the page (or click the drop zone to browse for your file).
  4. The chart updates automatically. Repeat for additional accounts or time periods.

CSV is a flexible format that is not standardized. This tool will nonetheless detect common bank CSV formats automatically. If your bank uses an unfamiliar layout, a configuration panel will appear so you can tell the tool which columns contain the date, amount, balance, and currency.

The tool can handle exports which do not include running account balances, however in this case you will also need to provide the initial balance directly into the tool, so that they may be calculated.

Multi-currency accounts (such as Wise) are supported: per-currency balances are extracted from the export and plotted as separate series. The tool handles a wide range of CSV layouts automatically (it supports 3 date formats, 3 delimiters, 2 number formats, 4 methods of specifying transactions, common column names in 4 languages, lack of running balances, and multi-currency csvs).

US Citizens Abroad

The tool is particularly useful for US tax filers with foreign bank accounts. FBAR and FATCA require use of the US Treasury end-of-year exchange rate, not a market rate. Use the "Fetch US Treasury year-end rates" button in the tool to load these rates automatically.

Disclaimer

This tool is provided for informational purposes only and does not constitute financial, tax, or legal advice. Balance calculations and currency conversions may contain errors. Exchange rates are sourced from third-party providers and may not match rates required for official reporting. You are solely responsible for verifying figures before using them in any financial, tax, or regulatory filing. The author makes no warranty and accepts no liability for errors, omissions, or any loss arising from use of this software.

Raw
balance_chart.html
<!DOCTYPE html>
<html lang="en">
<head>
<!--
Self-contained local HTML tool for visualizing bank balances across multiple accounts and currencies.
Usage: drop CSV exports from your banks onto the page. Common formats are auto-detected; an
interactive configuration modal appears for unrecognized formats. Balances are plotted daily;
currency conversion uses ECB exchange rates fetched daily or fixed annual rates from US Treasury
(and user modifiable). The bank data remain in your browser and are not sent over the internet.
The internet is only used to obtain exchange rates.
DISCLAIMER: This tool is provided for informational purposes only and does not constitute
financial, tax, or legal advice. Balance calculations and currency conversions may be inaccurate.
Do not rely on this tool for financial decisions. The author makes no warranty and accepts no
liability for errors, omissions, or any loss arising from use of this software.
THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT
NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE
SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
Copyright 2026 Analytics Alchemy, all rights reserved
-->
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Bank Balance Viewer</title>
<script src="https://cdn.plot.ly/plotly-2.35.2.min.js"></script>
<style>
*, *::before, *::after { box-sizing: border-box; margin: 0; padding: 0; }
body {
font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', sans-serif;
font-size: 14px;
background: #f5f5f5;
color: #333;
}
.container { max-width: 1200px; margin: 0 auto; padding: 24px; }
h1 { font-size: 20px; font-weight: 600; margin-bottom: 20px; color: #1a1a2e; }
.controls {
display: flex;
gap: 16px;
align-items: center;
margin-bottom: 20px;
flex-wrap: wrap;
}
.control-group { display: flex; align-items: center; gap: 8px; }
label { font-weight: 500; color: #555; }
select {
border: 1px solid #ccc;
border-radius: 4px;
padding: 6px 10px;
font-size: 14px;
background: white;
cursor: pointer;
}
.btn-group {
display: flex;
border: 1px solid #ccc;
border-radius: 4px;
overflow: hidden;
}
.btn-group button {
border: none;
padding: 6px 14px;
font-size: 14px;
cursor: pointer;
background: white;
color: #555;
transition: background 0.15s;
}
.btn-group button.active { background: #3b82f6; color: white; }
.btn-group button:hover:not(.active) { background: #eff6ff; }
.dropzone {
border: 2px dashed #bbb;
border-radius: 8px;
padding: 28px;
text-align: center;
color: #999;
background: white;
cursor: pointer;
margin-bottom: 20px;
transition: border-color 0.2s, background 0.2s;
user-select: none;
}
.dropzone.drag-over { border-color: #3b82f6; background: #eff6ff; color: #3b82f6; }
.dropzone p { font-size: 15px; margin-bottom: 6px; }
.dropzone small { font-size: 12px; }
#file-input { display: none; }
.accounts-section { margin-bottom: 20px; }
.accounts-section h2 {
font-size: 12px;
font-weight: 600;
color: #888;
margin-bottom: 10px;
text-transform: uppercase;
letter-spacing: 0.06em;
}
.account-row {
display: flex;
align-items: center;
gap: 12px;
background: white;
border: 1px solid #e0e0e0;
border-radius: 6px;
padding: 10px 14px;
margin-bottom: 8px;
}
.account-row.has-ib { flex-direction: column; align-items: stretch; }
.account-row-main { display: flex; align-items: center; gap: 12px; }
.color-dot {
width: 10px;
height: 10px;
border-radius: 50%;
flex-shrink: 0;
}
.account-name-input {
font-size: 14px;
border: 1px solid transparent;
border-radius: 4px;
padding: 4px 8px;
width: 200px;
font-weight: 500;
color: #333;
background: transparent;
}
.account-name-input:hover, .account-name-input:focus {
border-color: #ccc;
background: white;
outline: none;
}
.account-meta { color: #777; font-size: 13px; flex: 1; }
.account-meta .dates { font-variant-numeric: tabular-nums; }
.series-toggles { display: flex; gap: 8px; align-items: center; }
.series-toggle-label {
display: flex;
align-items: center;
gap: 4px;
font-size: 13px;
font-weight: 600;
color: #3b82f6;
cursor: pointer;
user-select: none;
white-space: nowrap;
}
.series-toggle-label input[type="checkbox"] { cursor: pointer; width: 14px; height: 14px; }
.remove-btn {
background: none;
border: none;
color: #ccc;
font-size: 18px;
cursor: pointer;
line-height: 1;
padding: 2px 6px;
border-radius: 3px;
}
.remove-btn:hover { background: #fee2e2; color: #ef4444; }
.config-btn {
background: none; border: none; color: #ccc;
font-size: 14px; cursor: pointer; line-height: 1;
padding: 2px 5px; border-radius: 3px;
}
.config-btn:hover { background: #eff6ff; color: #3b82f6; }
/* Initial balance row (transaction-mode accounts) */
.initial-balances {
display: flex;
align-items: center;
gap: 8px;
flex-wrap: wrap;
padding: 6px 0 2px 22px;
border-top: 1px solid #f0f0f0;
margin-top: 4px;
}
.ib-label { font-size: 12px; color: #888; font-weight: 500; white-space: nowrap; }
.ib-entry { display: flex; align-items: center; gap: 4px; }
.ib-ccy {
font-size: 12px; font-weight: 600; color: #555;
background: #f0f0f0; border: 1px solid #ddd; border-radius: 3px;
padding: 2px 6px; white-space: nowrap;
}
.ib-amount {
width: 100px; font-size: 12px;
border: 1px solid #ddd; border-radius: 3px; padding: 3px 6px;
}
.ib-amount.ib-zero { color: #dc2626; }
.warning {
background: #fef3c7;
border: 1px solid #fcd34d;
border-radius: 6px;
padding: 12px 16px;
margin-bottom: 16px;
font-size: 13px;
color: #92400e;
display: none;
}
.warning.visible { display: block; }
.rate-input-row {
display: flex;
align-items: center;
gap: 8px;
margin-top: 10px;
}
.rate-input-row input[type="number"] {
width: 100px;
border: 1px solid #ccc;
border-radius: 4px;
padding: 5px 8px;
font-size: 13px;
}
.rate-input-row button {
padding: 5px 12px;
background: #3b82f6;
color: white;
border: none;
border-radius: 4px;
cursor: pointer;
font-size: 13px;
}
.rate-input-row button:hover { background: #2563eb; }
#chart-wrap { background: white; border-radius: 8px; border: 1px solid #e0e0e0; }
.chart-note { font-size: 11px; color: #bbb; padding: 0 14px 12px; }
.empty-state {
text-align: center;
color: #bbb;
padding: 60px 0;
font-size: 15px;
background: white;
border-radius: 8px;
border: 1px solid #e0e0e0;
}
#rate-status {
font-size: 12px;
color: #888;
margin-left: 4px;
display: inline-flex;
align-items: center;
gap: 6px;
}
#rate-status.loading { color: #3b82f6; }
#rate-status.error { color: #ef4444; }
@keyframes spin { to { transform: rotate(360deg); } }
.spinner {
width: 12px; height: 12px;
border: 2px solid currentColor;
border-top-color: transparent;
border-radius: 50%;
display: inline-block;
animation: spin 0.7s linear infinite;
}
/* ── Fixed Rates Panel ───────────────────────────────── */
#fixed-rates-panel {
background: white;
border: 1px solid #e0e0e0;
border-radius: 6px;
padding: 12px 16px;
margin-bottom: 20px;
display: flex;
flex-wrap: wrap;
align-items: center;
gap: 8px;
}
#fixed-rates-rows { display: flex; flex-wrap: wrap; gap: 6px 16px; flex: 1; align-items: center; }
.fixed-rate-entry { display: flex; align-items: center; gap: 5px; }
.fixed-rate-label { font-size: 12px; color: #888; white-space: nowrap; }
.fixed-rate-input {
width: 84px; font-size: 13px;
border: 1px solid #ccc; border-radius: 4px; padding: 4px 7px;
}
.fixed-rate-ccy { font-size: 13px; font-weight: 600; color: #333; min-width: 32px; }
.fixed-rates-footer { display: flex; align-items: center; gap: 10px; flex-shrink: 0; }
#btn-fetch-fbar {
padding: 5px 12px; font-size: 12px;
background: white; color: #555;
border: 1px solid #ccc; border-radius: 4px; cursor: pointer; white-space: nowrap;
}
#btn-fetch-fbar:hover { background: #f5f5f5; }
#fbar-status { font-size: 12px; color: #888; }
.per-year-collapse-label { font-size: 12px; color: #555; display: flex; align-items: center; gap: 5px; margin-bottom: 8px; cursor: pointer; }
#fixed-rates-rows.per-year { display: block; }
.per-year-table { border-collapse: collapse; font-size: 13px; }
.per-year-th { font-size: 11px; color: #888; font-weight: normal; padding: 0 4px 4px; text-align: center; min-width: 96px; }
.per-year-td { padding: 3px 4px; text-align: center; white-space: nowrap; }
.per-year-ccy-label { font-size: 12px; color: #888; white-space: nowrap; padding-right: 10px; }
.per-year-input { width: 78px; font-size: 13px; border: 1px solid #ccc; border-radius: 4px; padding: 4px 6px; }
.treasury-badge { font-size: 10px; color: #1d4ed8; background: #dbeafe; border-radius: 3px; padding: 1px 4px; margin-left: 3px; vertical-align: middle; }
/* ── Configuration Modal ─────────────────────────────── */
.modal-backdrop {
position: fixed; inset: 0;
background: rgba(0,0,0,0.45);
display: flex; align-items: center; justify-content: center;
z-index: 1000;
padding: 20px;
}
.modal-panel {
background: white;
border-radius: 8px;
box-shadow: 0 20px 40px rgba(0,0,0,0.25);
width: 100%; max-width: 740px;
max-height: 90vh;
display: flex; flex-direction: column;
overflow: hidden;
}
.modal-header {
display: flex; justify-content: space-between; align-items: center;
padding: 14px 20px;
border-bottom: 1px solid #e0e0e0;
flex-shrink: 0;
}
.modal-header span { font-weight: 600; font-size: 15px; color: #1a1a2e; overflow: hidden; text-overflow: ellipsis; white-space: nowrap; }
.modal-close {
background: none; border: none; font-size: 22px; cursor: pointer;
color: #aaa; padding: 0 4px; line-height: 1; flex-shrink: 0;
}
.modal-close:hover { color: #ef4444; }
.modal-body { padding: 16px 20px; overflow-y: auto; flex: 1; }
.modal-section { margin-bottom: 16px; }
.modal-row { display: flex; align-items: center; gap: 10px; flex-wrap: wrap; }
.modal-section-label {
font-size: 11px; font-weight: 700; color: #888;
text-transform: uppercase; letter-spacing: 0.06em;
display: block; margin-bottom: 7px;
}
.modal-row .modal-section-label { margin-bottom: 0; min-width: 110px; }
.radio-group { display: flex; gap: 14px; flex-wrap: wrap; }
.radio-group label { display: flex; align-items: center; gap: 5px; font-size: 13px; cursor: pointer; font-weight: normal; color: #333; }
.modal-preview-scroll { overflow-x: auto; border: 1px solid #e0e0e0; border-radius: 4px; }
.modal-preview-table {
border-collapse: collapse; font-size: 12px;
width: 100%; white-space: nowrap;
}
.modal-preview-table th {
background: #f5f5f5; padding: 5px 10px;
border: 1px solid #e0e0e0; font-weight: 600; color: #555;
}
.modal-preview-table td { padding: 4px 10px; border: 1px solid #eee; color: #666; }
.col-roles-grid {
display: grid;
grid-template-columns: repeat(2, 1fr);
gap: 5px;
max-height: 220px;
overflow-y: auto;
border: 1px solid #eee;
border-radius: 4px;
padding: 8px;
background: #fafafa;
}
.col-role-row { display: flex; align-items: center; gap: 6px; }
.col-role-name {
font-size: 12px; color: #444; flex: 1;
overflow: hidden; text-overflow: ellipsis; white-space: nowrap;
max-width: 130px;
}
.col-role-select {
font-size: 12px; padding: 3px 6px;
border: 1px solid #ccc; border-radius: 4px; background: white;
cursor: pointer;
}
.info-icon {
cursor: pointer; color: #6b7280; font-style: normal;
margin-left: 6px; font-size: 13px; user-select: none;
}
.info-icon:hover { color: #2563eb; }
.col-roles-help {
background: #f0f7ff; border: 1px solid #bfdbfe; border-radius: 6px;
padding: 10px 14px; margin-bottom: 10px; font-size: 12px; line-height: 1.55; color: #333;
}
.col-roles-help strong { color: #1e40af; }
.col-roles-help ul { margin: 4px 0 0 0; padding-left: 18px; }
.dir-map-row { display: flex; align-items: center; gap: 10px; margin-bottom: 4px; }
.dir-map-value { font-size: 12px; font-weight: 600; color: #444; min-width: 80px; }
.dir-map-select {
font-size: 12px; padding: 2px 6px;
border: 1px solid #ccc; border-radius: 4px; background: white;
}
.modal-multiccy-label { font-size: 12px; color: #555; display: flex; align-items: center; gap: 5px; cursor: pointer; margin-top: 4px; }
.modal-text-input {
border: 1px solid #ccc; border-radius: 4px;
padding: 5px 8px; font-size: 13px; background: white;
}
.modal-footer {
display: flex; align-items: center; justify-content: flex-end; gap: 10px;
padding: 12px 20px;
border-top: 1px solid #e0e0e0;
flex-shrink: 0;
}
.modal-error { color: #ef4444; font-size: 13px; margin-right: auto; }
.btn-primary {
padding: 7px 18px; background: #3b82f6; color: white;
border: none; border-radius: 4px; cursor: pointer; font-size: 14px; font-weight: 500;
}
.btn-primary:hover { background: #2563eb; }
.modal-footer button:not(.btn-primary) {
padding: 7px 14px; background: white; color: #555;
border: 1px solid #ccc; border-radius: 4px; cursor: pointer; font-size: 14px;
}
.modal-footer button:not(.btn-primary):hover { background: #f5f5f5; }
</style>
</head>
<body>
<div class="container">
<h1>Bank Balance Viewer</h1>
<div class="warning" id="api-warning">
<strong>Exchange rate API unavailable.</strong>
Some currency conversions may be missing from the chart.
</div>
<div class="dropzone" id="dropzone">
<p>Drop CSV files here, or click to select</p>
<small>Supports most bank CSV exports — auto-detects format, or configure manually if needed</small>
<input type="file" id="file-input" accept=".csv" multiple>
</div>
<div class="accounts-section" id="accounts-section" style="display:none">
<h2>Accounts</h2>
<div id="account-list"></div>
</div>
<div id="controls-bar" style="display:none">
<div class="controls">
<div class="control-group">
<label for="display-currency">Display currency:</label>
<select id="display-currency"></select>
</div>
<div class="control-group">
<label>View:</label>
<div class="btn-group">
<button id="btn-individual" class="active" onclick="setViewMode('individual')">Individual</button>
<button id="btn-sum" onclick="setViewMode('sum')">Sum</button>
</div>
</div>
<div class="control-group">
<label for="rate-source">💱:</label>
<select id="rate-source">
<option value="daily" selected>Daily rates</option>
<option value="fixed">Fixed rates</option>
</select>
</div>
<div id="rate-status"></div>
</div>
</div>
<div id="fixed-rates-panel" style="display:none">
<div id="fixed-rates-rows"></div>
<div class="fixed-rates-footer">
<button id="btn-fetch-fbar">Fetch US Treasury year-end rates (used for FBAR)</button>
<span id="fbar-status"></span>
</div>
</div>
<div id="empty-state" class="empty-state">Drop bank transaction CSV files above to get started</div>
<div id="chart-wrap" style="display:none">
<div id="chart"></div>
<p class="chart-note">Balances are end-of-day values. Multiple transactions on the same day are collapsed to the final balance.
Daily rates <a href="https://frankfurter.dev/providers/" target=_blank>come from central banks</a></p>
<p class="chart-note" id="rate-note" style="display:none"></p>
<p class="chart-note" id="stale-note" style="display:none;color:#b45309"></p>
</div>
</div>
<!-- CSV Configuration Modal -->
<div id="config-modal" class="modal-backdrop" style="display:none">
<div class="modal-panel">
<div class="modal-header">
<span id="modal-title">Configure CSV</span>
<button class="modal-close" id="modal-cancel-x" title="Cancel">&times;</button>
</div>
<div class="modal-body">
<div class="modal-section">
<span class="modal-section-label">Preview (first 5 rows)</span>
<div id="modal-preview"></div>
</div>
<div class="modal-section modal-row">
<span class="modal-section-label">Delimiter</span>
<div class="radio-group" id="modal-delimiters">
<label><input type="radio" name="modal-delim" value="auto" checked> Auto</label>
<label><input type="radio" name="modal-delim" value=";"> Semicolon (;)</label>
<label><input type="radio" name="modal-delim" value=","> Comma (,)</label>
<label><input type="radio" name="modal-delim" value="&#9;"> Tab</label>
<label><input type="radio" name="modal-delim" value="|"> Pipe (|)</label>
</div>
</div>
<div class="modal-section">
<span class="modal-section-label">Column roles <span id="col-roles-info" class="info-icon" title="Help">&#x1F6C8;</span></span>
<div id="col-roles-help" class="col-roles-help" style="display:none">
Banks export transactions in different formats. Choose one set of column roles to match yours:<br><br>
<strong>Balance</strong> &mdash; A running balance column. Simplest case; no amounts needed.<br>
<strong>Amount (&plusmn;)</strong> &mdash; A single column with signed values (positive&nbsp;=&nbsp;credit, negative&nbsp;=&nbsp;debit).<br>
<strong>Credit + Debit</strong> &mdash; Two separate columns, one for incoming amounts, one for outgoing.<br>
<strong>Amount + Direction</strong> &mdash; An unsigned amount column plus a separate column indicating direction
(e.g.&nbsp;IN/OUT, Credit/Debit). After assigning Direction, configure which values mean credit vs.&nbsp;debit below.<br><br>
A <strong>Date</strong> column is always required. <strong>Currency</strong> and <strong>Account</strong> are optional.<br><br>
<strong>Multi-currency accounts</strong> (e.g.&nbsp;Wise, Revolut) &mdash;
<ul>
<li>If each currency has its own balance column, simply select Balance for each.</li>
<li>For accounts where each row has a source and a target amount in potentially different currencies,
enable the toggle below and assign Source/Target Amount and Currency columns, plus Direction.</li>
</ul>
</div>
<div id="modal-col-roles" class="col-roles-grid"></div>
</div>
<div id="modal-direction-section" class="modal-section" style="display:none">
<span class="modal-section-label">Direction values</span>
<div id="modal-direction-map"></div>
</div>
<div class="modal-section modal-row">
<label class="modal-multiccy-label">
<input type="checkbox" id="modal-multiccy-check"> Multi-currency transfers
</label>
</div>
<div class="modal-section modal-row">
<span class="modal-section-label">Date format</span>
<select id="modal-date-format" class="modal-text-input" style="width:auto">
<option value="YYYY-MM-DD">YYYY-MM-DD</option>
<option value="DD/MM/YYYY">DD/MM/YYYY (also DD-MM-YYYY, DD.MM.YYYY)</option>
<option value="MM/DD/YYYY">MM/DD/YYYY</option>
</select>
</div>
<div class="modal-section modal-row">
<span class="modal-section-label">Number format</span>
<div class="radio-group">
<label><input type="radio" name="modal-numfmt" value="standard" checked> Standard &nbsp;<small style="color:#aaa">1,234.56</small></label>
<label><input type="radio" name="modal-numfmt" value="european"> European &nbsp;<small style="color:#aaa">1.234,56</small></label>
</div>
</div>
<div class="modal-section modal-row" id="modal-ccy-section">
<span class="modal-section-label">Default currency</span>
<input type="text" id="modal-default-ccy" class="modal-text-input" value="EUR" maxlength="10" style="width:70px" placeholder="EUR">
</div>
<div class="modal-section modal-row">
<span class="modal-section-label">Account name</span>
<input type="text" id="modal-account-name" class="modal-text-input" style="width:200px" placeholder="e.g. Checking">
</div>
</div>
<div class="modal-footer">
<span id="modal-error" class="modal-error"></span>
<button id="modal-cancel">Cancel</button>
<button id="modal-import" class="btn-primary">Import</button>
</div>
</div>
</div>
<script>
// ─────────────────────────────────────────────────────────────
// State
// ─────────────────────────────────────────────────────────────
const state = {
// accounts: [{id, name, series, transactionMode, rawText, config, initialBalances}]
// series: [{currency, points: [{date, balance}], enabled}]
accounts: [],
displayCurrency: 'USD',
viewMode: 'individual',
rateSource: 'daily', // 'daily' | 'fixed'
rates: {}, // Frankfurter: { "EUR/USD": {"2025-01-01": 1.0843, ...} }
treasuryRates: {}, // Treasury: { "EUR": {"2025": 0.851, ...} } foreign-per-USD
fixedRates: {}, // Manual / single-rate: { "EUR": 0.92, ... } foreign-per-USD
fixedRatesPerYear: {}, // Per-year: { "EUR": { "2024": 0.92, "2025": 0.88 }, ... }
perYearMode: false, // true when per-year fixed rates are active
apiError: false,
};
let nextId = 0;
const COLORS = ['#3b82f6','#10b981','#f59e0b','#ef4444','#8b5cf6','#ec4899','#06b6d4','#84cc16'];
// ─────────────────────────────────────────────────────────────
// Generic CSV Detection & Parsing
// ─────────────────────────────────────────────────────────────
// RFC-4180-aware field splitter — handles quoted fields and any delimiter.
function parseLine(line, delimiter) {
const result = [];
let inQuotes = false;
let field = '';
for (let i = 0; i < line.length; i++) {
const ch = line[i];
if (ch === '"') {
if (inQuotes && line[i + 1] === '"') { field += '"'; i++; }
else inQuotes = !inQuotes;
} else if (ch === delimiter && !inQuotes) {
result.push(field.trim());
field = '';
} else {
field += ch;
}
}
result.push(field.trim());
return result;
}
// Return the delimiter with the highest count in the header line.
function detectDelimiter(headerLine) {
const candidates = [';', ',', '\t', '|'];
let best = ',', bestCount = 0;
for (const d of candidates) {
const count = headerLine.split(d).length - 1;
if (count > bestCount) { bestCount = count; best = d; }
}
return best;
}
// Parse a numeric string in either standard (1,234.56) or European (1.234,56) format.
// Strips currency symbols, whitespace, and handles bracketed negatives.
function parseNumber(s, format) {
const v = s.trim();
const negative = v.startsWith('-') || (v.startsWith('(') && v.endsWith(')'));
let d = v.replace(/[^0-9.,]/g, '');
if (!d) return NaN;
if (format === 'european') {
d = d.replace(/\./g, '').replace(',', '.');
} else {
d = d.replace(/,/g, '');
}
const n = parseFloat(d);
if (isNaN(n)) return NaN;
return (negative && n > 0) ? -n : n;
}
// Parse a date string to YYYY-MM-DD given a format hint.
// Handles trailing time components and any common separator character.
function parseDateGeneric(s, format) {
const v = s.trim().substring(0, 10);
if (!v) return null;
const parts = v.split(/[\-\/\.]/);
if (parts.length !== 3) return null;
let d, m, y;
// If first segment is 4 digits it's year-first regardless of format hint
if (parts[0].length === 4) {
[y, m, d] = parts;
} else if (format === 'MM/DD/YYYY') {
[m, d, y] = parts;
} else {
// DD/MM/YYYY default (also handles DD-MM-YYYY, DD.MM.YYYY)
[d, m, y] = parts;
}
if (!d || !m || !y) return null;
if (y.length === 2) y = (parseInt(y, 10) < 50 ? '20' : '19') + y;
return `${y}-${m.padStart(2, '0')}-${d.padStart(2, '0')}`;
}
// Column role aliases: English, Dutch, German.
const COL_ALIASES = {
date: ['date', 'value date', 'booking date', 'transaction date', 'posted date',
'datum', 'valutadatum', 'buchungsdatum', 'boekingsdatum', 'finished on', 'created on'],
balance: ['balance', 'saldo', 'running balance', 'closing balance', 'solde', 'kontostand'],
amount: ['amount', 'bedrag', 'betrag', 'montant', 'transaction amount', 'umsatz', 'debit/credit amount'],
credit: ['credit', 'credit amount', 'bij', 'haben', 'gutschrift'],
debit: ['debit', 'debit amount', 'af', 'soll', 'lastschrift'],
currency: ['currency', 'devise', 'munteenheid', 'währung', 'waehrung', 'ccy'],
account: ['account number', 'account', 'iban', 'rekeningnummer', 'kontonummer', 'account number/iban'],
direction: ['direction'],
sourceAmount: ['source amount', 'source amount (after fees)'],
sourceCurrency: ['source currency'],
targetAmount: ['target amount', 'target amount (after fees)'],
targetCurrency: ['target currency'],
};
// Detect column roles from header names. Returns {dateCol, balanceCol, balanceCurrencies,
// amountCol, creditCol, debitCol, currencyCol, accountCol, directionCol} with integer indices or null.
function detectColumns(headers) {
const result = {
dateCol: null, balanceCol: null, balanceCurrencies: null,
amountCol: null, creditCol: null, debitCol: null,
currencyCol: null, accountCol: null, directionCol: null,
sourceAmountCol: null, sourceCurrencyCol: null, targetAmountCol: null, targetCurrencyCol: null,
};
// Balance (XXX) / Saldo (XXX) multi-currency pattern
const balanceCols = [];
headers.forEach((h, i) => {
const m = h.match(/^(?:Balance|Saldo) \((\w+)\)$/i);
if (m) balanceCols.push({ col: i, ccy: m[1].toUpperCase() });
});
if (balanceCols.length > 0) result.balanceCurrencies = balanceCols;
for (const [role, aliases] of Object.entries(COL_ALIASES)) {
const idx = headers.findIndex(h => aliases.includes(h.toLowerCase().trim()));
if (idx === -1) continue;
if (role === 'date') result.dateCol = idx;
if (role === 'balance' && !result.balanceCurrencies) result.balanceCol = idx;
if (role === 'amount') result.amountCol = idx;
if (role === 'credit') result.creditCol = idx;
if (role === 'debit') result.debitCol = idx;
if (role === 'currency') result.currencyCol = idx;
if (role === 'account') result.accountCol = idx;
if (role === 'direction') result.directionCol = idx;
if (role === 'sourceAmount') result.sourceAmountCol = idx;
if (role === 'sourceCurrency') result.sourceCurrencyCol = idx;
if (role === 'targetAmount') result.targetAmountCol = idx;
if (role === 'targetCurrency') result.targetCurrencyCol = idx;
}
return result;
}
// Detect number format by sampling data rows. Returns 'european' or 'standard'.
function detectNumberFormat(rows, colIndex, delimiter) {
let europeanVotes = 0, standardVotes = 0;
for (let i = 0; i < Math.min(rows.length, 20); i++) {
const fields = parseLine(rows[i], delimiter);
const v = (fields[colIndex] || '').trim().replace(/[^0-9.,]/g, '');
if (!v) continue;
const lastComma = v.lastIndexOf(',');
const lastDot = v.lastIndexOf('.');
if (lastComma > lastDot) {
if (/\d{1,2}$/.test(v.substring(lastComma + 1))) europeanVotes++;
} else if (lastDot > lastComma) {
if (/\d{1,2}$/.test(v.substring(lastDot + 1))) standardVotes++;
}
}
return europeanVotes > standardVotes ? 'european' : 'standard';
}
// Detect date format by sampling data rows. Returns 'YYYY-MM-DD', 'DD/MM/YYYY', or 'MM/DD/YYYY'.
function detectDateFormat(rows, colIndex, delimiter) {
for (let i = 0; i < Math.min(rows.length, 10); i++) {
const fields = parseLine(rows[i], delimiter);
const v = (fields[colIndex] || '').trim().substring(0, 10);
if (!v) continue;
if (/^\d{4}[\-\/\.]/.test(v)) return 'YYYY-MM-DD';
const parts = v.split(/[\-\/\.]/);
if (parts.length !== 3) continue;
const first = parseInt(parts[0], 10);
const second = parseInt(parts[1], 10);
if (first > 12) return 'DD/MM/YYYY';
if (second > 12) return 'MM/DD/YYYY';
return 'DD/MM/YYYY'; // ambiguous, default to European
}
return 'YYYY-MM-DD';
}
// Find how many leading lines to skip (some banks prepend metadata before the header).
function detectSkipRows(lines, delimiter) {
if (lines.length < 2) return 0;
const counts = lines.slice(0, Math.min(6, lines.length)).map(l => parseLine(l, delimiter).length);
// Mode of all-but-first lines is the expected column count
const rest = counts.slice(1).slice().sort((a, b) => a - b);
const mode = rest[Math.floor(rest.length / 2)] || counts[0];
for (let i = 0; i < counts.length; i++) {
if (counts[i] >= mode - 1) return i;
}
return 0;
}
const DIRECTION_CREDIT = new Set(['IN', 'CREDIT', 'CR', 'C', '+']);
const DIRECTION_DEBIT = new Set(['OUT', 'DEBIT', 'DR', 'D', '-']);
const DIRECTION_BOTH = new Set(['NEUTRAL', 'CONVERSION']);
// Module-level role option lists used by the modal.
const ROLE_OPTIONS_BASE = [
{ v: '', l: '— ignore —' },
{ v: 'date', l: 'Date' },
{ v: 'balance', l: 'Balance' },
{ v: 'amount', l: 'Amount (±)' },
{ v: 'direction', l: 'Direction' },
{ v: 'credit', l: 'Credit (separate col.)' },
{ v: 'debit', l: 'Debit (separate col.)' },
{ v: 'currency', l: 'Currency' },
{ v: 'account', l: 'Account' },
];
const ROLE_OPTIONS_MULTI = [
...ROLE_OPTIONS_BASE,
{ v: 'sourceAmount', l: 'Source Amount' },
{ v: 'sourceCurrency', l: 'Source Currency' },
{ v: 'targetAmount', l: 'Target Amount' },
{ v: 'targetCurrency', l: 'Target Currency' },
];
// Returns true if more than one distinct target currency appears in the data rows.
// Used to distinguish a true multi-currency export from a single-currency export
// that happens to carry source/target amount columns (e.g. Wise single-balance export).
function dataHasMultipleCurrencies(dataRows, targetCurrencyColIdx, delimiter) {
if (targetCurrencyColIdx == null) return false;
const seen = new Set();
for (let i = 0; i < Math.min(dataRows.length, 200); i++) {
const ccy = (parseLine(dataRows[i], delimiter)[targetCurrencyColIdx] || '').trim().toUpperCase();
if (ccy) seen.add(ccy);
if (seen.size > 1) return true;
}
return false;
}
// Build a default direction map from actual data values.
// Returns { value: 'credit'|'debit'|'both'|'skip' }.
function buildDefaultDirectionMap(dataRows, colIndex, delimiter, multiCurrency = false) {
const values = new Set();
for (let i = 0; i < Math.min(dataRows.length, 100); i++) {
const f = parseLine(dataRows[i], delimiter);
const v = (f[colIndex] || '').trim();
if (v) values.add(v);
}
const map = {};
for (const v of values) {
const upper = v.toUpperCase();
if (DIRECTION_CREDIT.has(upper)) map[v] = 'credit';
else if (DIRECTION_DEBIT.has(upper)) map[v] = 'debit';
else if (multiCurrency && DIRECTION_BOTH.has(upper)) map[v] = 'both';
else map[v] = 'credit'; // unknown defaults to credit
}
return map;
}
// Returns true if a direction map contains only fully-recognized terms (no unknowns requiring user input).
function directionMapIsAutoResolved(map, multiCurrency = false) {
for (const v of Object.keys(map)) {
const upper = v.toUpperCase();
if (!DIRECTION_CREDIT.has(upper) && !DIRECTION_DEBIT.has(upper) &&
!(multiCurrency && DIRECTION_BOTH.has(upper))) return false;
}
return true;
}
// Auto-detect as much as possible. Returns {config, confident}.
// confident = true means we have enough info to parse without showing the modal.
function buildConfig(text) {
const allLines = text.split(/\r\n|\r|\n/).filter(l => l.trim().length > 0);
if (allLines.length < 2) return { config: null, confident: false };
const delimiter = detectDelimiter(allLines[0]);
const skipRows = detectSkipRows(allLines, delimiter);
const lines = allLines.slice(skipRows);
if (lines.length < 2) return { config: null, confident: false };
const headers = parseLine(lines[0], delimiter).map(h => h.trim());
const cols = detectColumns(headers);
const dataRows = lines.slice(1);
// Pick a representative column for number/date format detection
const numCol = cols.balanceCurrencies ? cols.balanceCurrencies[0].col
: cols.balanceCol ?? cols.amountCol ?? cols.creditCol ?? cols.debitCol;
const numberFormat = numCol != null ? detectNumberFormat(dataRows, numCol, delimiter) : 'standard';
const dateFormat = cols.dateCol != null ? detectDateFormat(dataRows, cols.dateCol, delimiter) : 'YYYY-MM-DD';
// Derive default name from account column
let name = null;
if (cols.accountCol != null && dataRows.length > 0) {
const firstRow = parseLine(dataRows[0], delimiter);
const acct = (firstRow[cols.accountCol] || '').replace(/\s/g, '');
if (acct.length >= 4) name = acct.slice(-4);
}
// If the file has source/target cols but only one target currency appears in the data,
// it's a single-currency export from a multi-currency bank (e.g. Wise single-balance export).
// Remap target amount/currency to the plain amount/currency cols so the simpler path is used,
// avoiding a spurious second series for the source currency.
const hasSourceTarget = cols.targetAmountCol != null && cols.sourceAmountCol != null;
const isMultiCcy = hasSourceTarget &&
dataHasMultipleCurrencies(dataRows, cols.targetCurrencyCol, delimiter);
if (hasSourceTarget && !isMultiCcy) {
cols.amountCol = cols.targetAmountCol;
cols.currencyCol = cols.targetCurrencyCol ?? cols.currencyCol;
cols.targetAmountCol = null;
cols.sourceAmountCol = null;
cols.targetCurrencyCol = null;
cols.sourceCurrencyCol = null;
}
// Build default direction map if direction column was detected
let directionMap = null;
if (cols.directionCol != null) {
directionMap = buildDefaultDirectionMap(dataRows, cols.directionCol, delimiter, isMultiCcy);
}
const config = {
delimiter, skipRows,
dateCol: cols.dateCol,
balanceCol: cols.balanceCol,
balanceCurrencies: cols.balanceCurrencies,
amountCol: cols.amountCol,
creditCol: cols.creditCol,
debitCol: cols.debitCol,
directionCol: cols.directionCol,
directionMap,
sourceAmountCol: cols.sourceAmountCol,
sourceCurrencyCol: cols.sourceCurrencyCol,
targetAmountCol: cols.targetAmountCol,
targetCurrencyCol: cols.targetCurrencyCol,
currencyCol: cols.currencyCol,
accountCol: cols.accountCol,
defaultCurrency: 'EUR',
dateFormat, numberFormat,
initialBalances: {},
name,
};
const hasDate = config.dateCol != null;
const hasBalance = config.balanceCol != null || config.balanceCurrencies != null;
const hasTxn = config.amountCol != null || config.creditCol != null || config.debitCol != null;
const hasMultiCcy = config.targetAmountCol != null && config.sourceAmountCol != null;
const confident = hasDate && (hasBalance || hasTxn || hasMultiCcy);
return { config, confident };
}
// Parse a CSV file into {name, series} given a config object (see detectConfig).
// Supports four modes depending on which config fields are set:
// 1. balanceCurrencies — multiple balance columns, one per currency (e.g. Wise multi-currency export)
// 2. balanceCol — single balance column; currency from currencyCol or defaultCurrency
// 3. targetAmountCol + sourceAmountCol — multi-currency transfer rows; each row may update
// two running balances (target credit and/or source debit)
// 4. amountCol / creditCol+debitCol — single-currency transaction rows; accumulates a
// running balance, with optional directionCol to sign amounts
// All modes emit {date → balance} per currency, then delegate to buildSeries().
function parseGeneric(text, config) {
const allLines = text.split(/\r\n|\r|\n/).filter(l => l.trim().length > 0);
const lines = allLines.slice(config.skipRows);
if (lines.length < 2) return null;
let dataRows = lines.slice(1);
// Detect and fix reverse-chronological order in transaction mode
const isTransactionMode = config.balanceCol == null && config.balanceCurrencies == null;
if (isTransactionMode && config.dateCol != null && dataRows.length >= 2) {
const firstDate = parseDateGeneric((parseLine(dataRows[0], config.delimiter)[config.dateCol] || ''), config.dateFormat);
const lastDate = parseDateGeneric((parseLine(dataRows[dataRows.length - 1], config.delimiter)[config.dateCol] || ''), config.dateFormat);
if (firstDate && lastDate && firstDate > lastDate) {
dataRows = [...dataRows].reverse();
}
}
const byCurrency = {};
const runningBalances = { ...config.initialBalances };
// Pre-initialize currency buckets for multi-balance-column mode
if (config.balanceCurrencies) {
config.balanceCurrencies.forEach(({ ccy }) => { byCurrency[ccy] = {}; });
}
for (const row of dataRows) {
const f = parseLine(row, config.delimiter);
const date = config.dateCol != null
? parseDateGeneric(f[config.dateCol] || '', config.dateFormat)
: null;
if (!date || date.length < 10) continue;
if (config.balanceCurrencies) {
// Multiple Balance(XXX) columns — one per currency
for (const { col, ccy } of config.balanceCurrencies) {
const raw = (f[col] || '').trim();
if (!raw || raw === '?') continue;
const balance = parseNumber(raw, config.numberFormat);
if (isNaN(balance)) continue;
byCurrency[ccy][date] = balance;
}
} else if (config.balanceCol != null) {
// Single balance column
const raw = (f[config.balanceCol] || '').trim();
const balance = parseNumber(raw, config.numberFormat);
if (isNaN(balance)) continue;
const ccy = config.currencyCol != null
? ((f[config.currencyCol] || '').trim().toUpperCase() || config.defaultCurrency)
: config.defaultCurrency;
if (!byCurrency[ccy]) byCurrency[ccy] = {};
byCurrency[ccy][date] = balance;
} else if (config.targetAmountCol != null && config.sourceAmountCol != null) {
// Multi-currency transfer mode: each row may affect two currency balances
const dirRaw = config.directionCol != null ? (f[config.directionCol] || '').trim() : null;
let mapped = null;
if (dirRaw && config.directionMap) {
mapped = config.directionMap[dirRaw] || config.directionMap[dirRaw.toUpperCase()];
}
if (mapped === 'skip') continue;
const tgtAmt = parseNumber(f[config.targetAmountCol] || '', config.numberFormat);
const srcAmt = parseNumber(f[config.sourceAmountCol] || '', config.numberFormat);
const tgtCcy = config.targetCurrencyCol != null
? ((f[config.targetCurrencyCol] || '').trim().toUpperCase() || config.defaultCurrency)
: config.defaultCurrency;
const srcCcy = config.sourceCurrencyCol != null
? ((f[config.sourceCurrencyCol] || '').trim().toUpperCase() || tgtCcy)
: tgtCcy;
// credit: add to target; debit: subtract from source; both/null: both effects, but skip source debit if same currency (no double-count)
const creditTarget = (mapped == null || mapped === 'credit' || mapped === 'both') && !isNaN(tgtAmt);
const debitSource = !isNaN(srcAmt) && (
mapped === 'debit' ||
((mapped == null || mapped === 'both') && srcCcy !== tgtCcy)
);
if (creditTarget) {
if (!(tgtCcy in runningBalances)) runningBalances[tgtCcy] = 0;
runningBalances[tgtCcy] = Math.round((runningBalances[tgtCcy] + tgtAmt) * 100) / 100;
if (!byCurrency[tgtCcy]) byCurrency[tgtCcy] = {};
byCurrency[tgtCcy][date] = runningBalances[tgtCcy];
}
if (debitSource) {
if (!(srcCcy in runningBalances)) runningBalances[srcCcy] = 0;
runningBalances[srcCcy] = Math.round((runningBalances[srcCcy] - srcAmt) * 100) / 100;
if (!byCurrency[srcCcy]) byCurrency[srcCcy] = {};
byCurrency[srcCcy][date] = runningBalances[srcCcy];
}
} else {
// Single-currency transaction mode: accumulate running balance
let delta = NaN;
if (config.amountCol != null) {
delta = parseNumber(f[config.amountCol] || '', config.numberFormat);
} else {
const credit = config.creditCol != null ? (parseNumber(f[config.creditCol] || '', config.numberFormat) || 0) : 0;
const debit = config.debitCol != null ? (parseNumber(f[config.debitCol] || '', config.numberFormat) || 0) : 0;
if (!isNaN(credit) || !isNaN(debit)) delta = (isNaN(credit) ? 0 : credit) - (isNaN(debit) ? 0 : debit);
}
// Apply direction column if present
if (config.directionCol != null && config.directionMap && !isNaN(delta)) {
const dirRaw = (f[config.directionCol] || '').trim();
const mapped = config.directionMap[dirRaw] || config.directionMap[dirRaw.toUpperCase()];
if (mapped === 'skip') delta = NaN;
else if (mapped === 'debit') delta = -Math.abs(delta);
else delta = Math.abs(delta); // credit or unmapped → positive
}
if (isNaN(delta)) continue;
const ccy = config.currencyCol != null
? ((f[config.currencyCol] || '').trim().toUpperCase() || config.defaultCurrency)
: config.defaultCurrency;
if (!(ccy in runningBalances)) runningBalances[ccy] = 0;
runningBalances[ccy] = Math.round((runningBalances[ccy] + delta) * 100) / 100;
if (!byCurrency[ccy]) byCurrency[ccy] = {};
byCurrency[ccy][date] = runningBalances[ccy];
}
}
const result = buildSeries(byCurrency);
if (result) result.name = config.name;
return result;
}
function buildSeries(byCurrency) {
const series = Object.entries(byCurrency)
.filter(([, byDate]) => Object.keys(byDate).length > 0)
.map(([currency, byDate]) => ({
currency,
points: Object.entries(byDate)
.map(([date, balance]) => ({ date, balance }))
.sort((a, b) => a.date.localeCompare(b.date))
}));
return series.length > 0 ? { series } : null;
}
// ─────────────────────────────────────────────────────────────
// Exchange Rates
// ─────────────────────────────────────────────────────────────
// ISO code → Treasury "country_currency_desc" label
const TREASURY_CCY = {
EUR: 'Euro Zone-Euro',
GBP: 'United Kingdom-Pound',
CHF: 'Switzerland-Franc',
JPY: 'Japan-Yen',
CAD: 'Canada-Dollar',
AUD: 'Australia-Dollar',
NZD: 'New Zealand-Dollar',
SEK: 'Sweden-Krona',
NOK: 'Norway-Krone',
DKK: 'Denmark-Krone',
HKD: 'Hong Kong-Dollar',
SGD: 'Singapore-Dollar',
};
// Dispatch to the appropriate lookup based on state.rateSource.
// Returns: 1 `from` = ? `to` on `date`, or null if unavailable.
function lookupRate(from, to, date) {
if (from === to) return 1;
if (state.rateSource === 'fixed') {
return state.perYearMode
? lookupRateByYear(state.fixedRatesPerYear, from, to, date)
: lookupRateFixed(from, to);
}
return lookupRateDaily(from, to, date);
}
// Fixed-rate lookup: cross through USD. state.fixedRates[ccy] = units of ccy per 1 USD.
function lookupRateFixed(from, to) {
if (from === to) return 1;
const rFrom = from === 'USD' ? 1 : state.fixedRates[from];
const rTo = to === 'USD' ? 1 : state.fixedRates[to];
if (!rFrom || !rTo) return null;
return rTo / rFrom;
}
// ── Daily (Frankfurter) ──────────────────────────────────────
function rateAtDate(rateMap, date) {
const sortedDates = Object.keys(rateMap).sort();
let result = null;
for (const d of sortedDates) {
if (d <= date) result = rateMap[d];
else break;
}
return result;
}
function lookupRateDaily(from, to, date) {
if (from === 'EUR') {
const m = state.rates[`EUR/${to}`];
if (m) { const r = rateAtDate(m, date); if (r !== null) return r; }
}
if (to === 'EUR') {
const m = state.rates[`EUR/${from}`];
if (m) { const r = rateAtDate(m, date); if (r !== null) return 1 / r; }
}
const mFrom = state.rates[`EUR/${from}`];
const mTo = state.rates[`EUR/${to}`];
if (mFrom && mTo) {
const rFrom = rateAtDate(mFrom, date);
const rTo = rateAtDate(mTo, date);
if (rFrom !== null && rTo !== null) return rTo / rFrom;
}
return null;
}
async function ensureRates(currencies, fromDate, toDate) {
const quotes = [...new Set(currencies)].filter(c => c !== 'EUR');
if (quotes.length === 0) return;
const needed = quotes.filter(c => {
const m = state.rates[`EUR/${c}`];
if (!m) return true;
const ds = Object.keys(m).sort();
return ds.length === 0 || ds[0] > fromDate || ds[ds.length - 1] < toDate;
});
if (needed.length === 0) return;
const url = `https://api.frankfurter.dev/v2/rates?base=EUR&quotes=${needed.join(',')}&from=${fromDate}&to=${toDate}`;
setStatus('loading', '<span class="spinner"></span> Fetching daily rates…');
try {
const resp = await fetch(url);
if (!resp.ok) throw new Error(`HTTP ${resp.status}`);
const data = await resp.json();
for (const entry of data) {
const key = `EUR/${entry.quote}`;
if (!state.rates[key]) state.rates[key] = {};
state.rates[key][entry.date] = entry.rate;
}
setStatus('', '');
} catch (err) {
console.warn('Frankfurter fetch failed:', err);
state.apiError = true;
setStatus('error', 'Daily rates unavailable');
document.getElementById('api-warning').classList.add('visible');
}
}
// ── Treasury year-end / per-year fixed ───────────────────────
// rateTable: { ISO: { calendarYear: foreignPerUSD } }
function lookupRateByYear(rateTable, from, to, date) {
const year = date.substring(0, 4);
const foreignPerUSD = (ccy) => {
if (ccy === 'USD') return 1;
const byYear = rateTable[ccy];
if (!byYear) return null;
const years = Object.keys(byYear).sort();
let r = null;
for (const y of years) { if (y <= year) r = byYear[y]; }
return r;
};
const rFrom = foreignPerUSD(from);
const rTo = foreignPerUSD(to);
if (rFrom === null || rTo === null) return null;
return rTo / rFrom;
}
// Returns the treasury (forward-filled) rate for ccy in the given year string.
function treasuryForYear(ccy, yr) {
const byYear = state.treasuryRates[ccy];
if (!byYear) return null;
const years = Object.keys(byYear).sort();
let r = null;
for (const y of years) { if (y <= yr) r = byYear[y]; }
return r;
}
// Returns all years from minYear to maxYear in the loaded data (inclusive, no gaps).
function getDataYears() {
let minYear = null, maxYear = null;
for (const acc of state.accounts)
for (const s of acc.series)
for (const p of s.points) {
const y = p.date.substring(0, 4);
if (!minYear || y < minYear) minYear = y;
if (!maxYear || y > maxYear) maxYear = y;
}
if (!minYear) return [];
const years = [];
for (let y = parseInt(minYear, 10); y <= parseInt(maxYear, 10); y++) years.push(String(y));
return years;
}
async function ensureTreasuryRates(currencies, fromDate, toDate) {
const nonUSD = [...new Set(currencies)].filter(c => c !== 'USD' && TREASURY_CCY[c]);
if (nonUSD.length === 0) return;
const fromYear = fromDate.substring(0, 4);
const toYear = toDate.substring(0, 4);
const needed = nonUSD.filter(c => {
const byYear = state.treasuryRates[c];
if (!byYear) return true;
const years = Object.keys(byYear).sort();
return !years.length || years[0] > fromYear || years[years.length - 1] < toYear;
});
if (needed.length === 0) return;
const TREAS = 'https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v1/accounting/od/rates_of_exchange';
const url = `${TREAS}?filter=record_date:gte:${fromYear}-12-31,record_date:lte:${toYear}-12-31&sort=record_date&page%5Bsize%5D=10000`;
setStatus('loading', '<span class="spinner"></span> Fetching Treasury rates…');
try {
const resp = await fetch(url);
if (!resp.ok) throw new Error(`HTTP ${resp.status}`);
const json = await resp.json();
const descToISO = Object.fromEntries(Object.entries(TREASURY_CCY).map(([iso, desc]) => [desc, iso]));
for (const entry of json.data) {
const iso = descToISO[entry.country_currency_desc];
if (!iso || !needed.includes(iso)) continue;
if (!state.treasuryRates[iso]) state.treasuryRates[iso] = {};
state.treasuryRates[iso][entry.record_calendar_year] = parseFloat(entry.exchange_rate);
}
setStatus('', '');
} catch (err) {
console.warn('Treasury fetch failed:', err);
setStatus('error', 'Treasury rates unavailable');
}
}
// ── Shared helpers ───────────────────────────────────────────
function setStatus(cls, html) {
const el = document.getElementById('rate-status');
el.className = cls;
el.innerHTML = html;
}
// ─────────────────────────────────────────────────────────────
// Conversion & Aggregation
// ─────────────────────────────────────────────────────────────
function convertSeries(points, from, to) {
if (from === to) return points;
const out = [];
for (const pt of points) {
const rate = lookupRate(from, to, pt.date);
if (rate !== null) out.push({ date: pt.date, balance: pt.balance * rate });
}
return out;
}
// Latest balance in sortedPoints at or before `date`; 0 if none.
function balanceBefore(sortedPoints, date) {
let val = 0;
for (const pt of sortedPoints) {
if (pt.date <= date) val = pt.balance;
else break;
}
return val;
}
// Sum all converted series, forward-filling gaps, at the union of all dates.
function computeSum(convertedSeriesList) {
const dateSet = new Set();
for (const pts of convertedSeriesList) pts.forEach(p => dateSet.add(p.date));
const dates = Array.from(dateSet).sort();
return dates.map(date => ({
date,
balance: convertedSeriesList.reduce((s, pts) => s + balanceBefore(pts, date), 0)
}));
}
// ─────────────────────────────────────────────────────────────
// Rendering
// ─────────────────────────────────────────────────────────────
function render() {
const ccy = state.displayCurrency;
const chartWrap = document.getElementById('chart-wrap');
const emptyState = document.getElementById('empty-state');
if (state.accounts.length === 0) {
chartWrap.style.display = 'none';
emptyState.style.display = 'block';
return;
}
emptyState.style.display = 'none';
chartWrap.style.display = 'block';
const traces = [];
let colorIdx = 0;
if (state.viewMode === 'individual') {
for (const account of state.accounts) {
const color = COLORS[colorIdx % COLORS.length];
for (const s of account.series) {
if (!s.enabled) continue;
const pts = convertSeries(s.points, s.currency, ccy);
if (pts.length === 0) continue;
const label = account.series.length > 1
? `${account.name} (${s.currency})`
: account.name;
traces.push({
x: pts.map(p => p.date),
y: pts.map(p => p.balance),
name: label,
mode: 'lines+markers',
type: 'scatter',
marker: { size: 5, color },
line: { color, shape: 'hv' },
hovertemplate: `<b>${escapeHtml(label)}</b><br>%{x|%Y-%m-%d}<br>%{y:,.2f} ${ccy}<extra></extra>`,
});
}
colorIdx++;
}
} else {
// Sum mode: collect all converted series
const allConverted = [];
for (const account of state.accounts) {
for (const s of account.series) {
if (!s.enabled) continue;
const pts = convertSeries(s.points, s.currency, ccy);
if (pts.length > 0) allConverted.push(pts);
}
}
if (allConverted.length > 0) {
const summed = computeSum(allConverted);
traces.push({
x: summed.map(p => p.date),
y: summed.map(p => p.balance),
name: `Total (${ccy})`,
mode: 'lines+markers',
type: 'scatter',
marker: { size: 5, color: COLORS[0] },
line: { color: COLORS[0], shape: 'hv' },
hovertemplate: `<b>Total</b><br>%{x|%Y-%m-%d}<br>%{y:,.2f} ${ccy}<extra></extra>`,
});
}
}
const layout = {
title: { text: `Account Balances (${ccy})`, font: { size: 16 }, x: 0.03 },
xaxis: { type: 'date', tickformat: '%Y-%m-%d' },
yaxis: { title: ccy, tickformat: ',.2f' },
hovermode: 'closest',
legend: { orientation: 'h', y: -0.2, x: 0 },
margin: { t: 50, r: 20, b: 80, l: 80 },
height: 480,
paper_bgcolor: 'white',
plot_bgcolor: '#fafafa',
font: { family: '-apple-system, BlinkMacSystemFont, "Segoe UI", sans-serif', size: 13 },
};
Plotly.react('chart', traces, layout, { responsive: true, displayModeBar: true });
// Warn if any account's data ends more than ~1 month before the most recent account.
const staleNote = document.getElementById('stale-note');
if (staleNote) {
const accountEnds = state.accounts.map(acc => {
const dates = acc.series.flatMap(s => s.points.map(p => p.date));
return { name: acc.name, last: dates.length ? dates.reduce((a, b) => a > b ? a : b) : null };
}).filter(a => a.last !== null);
if (accountEnds.length >= 2) {
const globalMax = accountEnds.reduce((a, b) => a.last > b.last ? a : b).last;
const gTime = new Date(globalMax).getTime();
const stale = accountEnds.filter(a => (gTime - new Date(a.last).getTime()) >= 30 * 24 * 60 * 60 * 1000);
if (stale.length > 0) {
const parts = stale.map(a => `${a.name} (last entry: ${a.last})`).join(', ');
staleNote.textContent = `⚠ Possible missing data: ${parts} — no transactions in the period up to ${globalMax}. Upload more recent data if available.`;
staleNote.style.display = '';
} else {
staleNote.style.display = 'none';
}
} else {
staleNote.style.display = 'none';
}
}
// Caveat: when using Treasury-derived per-year rates and data extends into the current
// calendar year, the prior year's year-end rate is forward-filled (no current-year rate yet).
const rateNote = document.getElementById('rate-note');
if (rateNote) {
const currentYear = String(new Date().getFullYear());
const dataYears = getDataYears();
const dataInCurrentYear = dataYears.length && dataYears[dataYears.length - 1] >= currentYear;
if (state.rateSource === 'fixed' && state.perYearMode && dataInCurrentYear) {
const priorYear = String(new Date().getFullYear() - 1);
rateNote.textContent = `Note: ${currentYear} data uses the ${priorYear} Treasury year-end exchange rate (no ${currentYear} year-end rate is available yet).`;
rateNote.style.display = '';
} else {
rateNote.style.display = 'none';
}
}
}
// ─────────────────────────────────────────────────────────────
// Account List UI
// ─────────────────────────────────────────────────────────────
function updateCurrencySelector() {
const sel = document.getElementById('display-currency');
const currencies = new Set(['USD']);
for (const acc of state.accounts)
for (const s of acc.series) currencies.add(s.currency);
const sorted = [...currencies].sort();
const current = state.displayCurrency;
sel.innerHTML = sorted
.map(c => `<option value="${c}"${c === current ? ' selected' : ''}>${c}</option>`)
.join('');
if (!currencies.has(current)) {
state.displayCurrency = 'USD';
sel.value = 'USD';
}
}
function updateAccountList() {
const section = document.getElementById('accounts-section');
const list = document.getElementById('account-list');
const controlsBar = document.getElementById('controls-bar');
if (state.accounts.length === 0) {
section.style.display = 'none';
controlsBar.style.display = 'none';
return;
}
section.style.display = 'block';
controlsBar.style.display = 'block';
updateCurrencySelector();
list.innerHTML = '';
const accountColors = {};
let ci = 0;
for (const acc of state.accounts) { accountColors[acc.id] = COLORS[ci++ % COLORS.length]; }
for (const account of state.accounts) {
const allPoints = account.series.flatMap(s => s.points);
const dates = allPoints.map(p => p.date).sort();
const dateRange = dates.length ? `${dates[0]} — ${dates[dates.length - 1]}` : 'no data';
const color = accountColors[account.id];
const togglesHtml = account.series.map(s => `
<label class="series-toggle-label">
<input type="checkbox" class="series-toggle"
data-account-id="${account.id}" data-ccy="${s.currency}"
${s.enabled ? 'checked' : ''}>
${escapeHtml(s.currency)}
</label>
`).join('');
const mainHtml = `
<div class="color-dot" style="background:${color}"></div>
<input class="account-name-input" value="${escapeHtml(account.name)}"
data-id="${account.id}" title="Click to rename" spellcheck="false">
<div class="series-toggles">${togglesHtml}</div>
<div class="account-meta"><span class="dates">${dateRange}</span></div>
<button class="config-btn" data-id="${account.id}" title="Reconfigure">&#9881;</button>
<button class="remove-btn" data-id="${account.id}" title="Remove">&times;</button>
`;
const row = document.createElement('div');
if (account.transactionMode) {
row.className = 'account-row has-ib';
// Build initial balance inputs — one per currency in the series
const currencies = account.series.map(s => s.currency);
const ibEntries = currencies.map(ccy => {
const val = account.initialBalances[ccy] ?? 0;
const zeroClass = val === 0 ? ' ib-zero' : '';
return `
<div class="ib-entry">
<span class="ib-ccy">${escapeHtml(ccy)}</span>
<input type="number" class="ib-amount${zeroClass}" step="0.01"
data-account-id="${account.id}" data-ccy="${escapeHtml(ccy)}"
value="${val.toFixed(2)}">
</div>`;
}).join('');
row.innerHTML = `
<div class="account-row-main">${mainHtml}</div>
<div class="initial-balances">
<span class="ib-label">Initial balance:</span>
${ibEntries}
</div>
`;
} else {
row.className = 'account-row';
row.innerHTML = mainHtml;
}
list.appendChild(row);
}
// Name editing
list.querySelectorAll('.account-name-input').forEach(input => {
input.addEventListener('input', e => {
const id = +e.target.dataset.id;
const acc = state.accounts.find(a => a.id === id);
if (acc) { acc.name = e.target.value; render(); }
});
});
// Series toggle
list.querySelectorAll('.series-toggle').forEach(cb => {
cb.addEventListener('change', e => {
const accountId = +e.target.dataset.accountId;
const ccy = e.target.dataset.ccy;
const acc = state.accounts.find(a => a.id === accountId);
if (acc) {
const s = acc.series.find(s => s.currency === ccy);
if (s) { s.enabled = e.target.checked; render(); }
}
});
});
// Reconfigure account
list.querySelectorAll('.config-btn').forEach(btn => {
btn.addEventListener('click', async e => {
const id = +e.target.dataset.id;
const acc = state.accounts.find(a => a.id === id);
if (!acc || !acc.rawText) return;
let newConfig;
try {
newConfig = await showConfigModal(acc.rawText, acc.config, acc.name);
} catch {
return; // cancelled
}
const parsed = parseGeneric(acc.rawText, newConfig);
if (!parsed) { alert('No data could be read with the new configuration.'); return; }
const enabledMap = {};
acc.series.forEach(s => { enabledMap[s.currency] = s.enabled; });
acc.config = newConfig;
acc.initialBalances = { ...newConfig.initialBalances };
acc.transactionMode = newConfig.balanceCol == null && newConfig.balanceCurrencies == null;
acc.series = parsed.series.map(s => ({
...s, enabled: s.currency in enabledMap ? enabledMap[s.currency] : true
}));
updateAccountList();
await refreshRates();
render();
});
});
// Remove account
list.querySelectorAll('.remove-btn').forEach(btn => {
btn.addEventListener('click', e => {
const id = +e.target.dataset.id;
state.accounts = state.accounts.filter(a => a.id !== id);
updateAccountList();
render();
});
});
// Initial balance change — re-parse and re-render
list.querySelectorAll('.ib-amount').forEach(input => {
input.addEventListener('change', e => {
const accountId = +e.target.dataset.accountId;
const ccy = e.target.dataset.ccy;
const acc = state.accounts.find(a => a.id === accountId);
if (!acc || !acc.rawText) return;
acc.initialBalances[ccy] = parseFloat(e.target.value) || 0;
e.target.classList.toggle('ib-zero', acc.initialBalances[ccy] === 0);
const newConfig = { ...acc.config, initialBalances: { ...acc.initialBalances } };
const parsed = parseGeneric(acc.rawText, newConfig);
if (parsed) {
const enabledMap = {};
acc.series.forEach(s => { enabledMap[s.currency] = s.enabled; });
acc.series = parsed.series.map(s => ({
...s, enabled: s.currency in enabledMap ? enabledMap[s.currency] : true
}));
}
updateAccountList();
refreshRates().then(render);
});
});
}
function escapeHtml(s) {
return String(s)
.replace(/&/g, '&amp;')
.replace(/</g, '&lt;')
.replace(/>/g, '&gt;')
.replace(/"/g, '&quot;');
}
// ─────────────────────────────────────────────────────────────
// File Processing
// ─────────────────────────────────────────────────────────────
async function processFiles(files) {
for (const file of files) {
if (!file.name.toLowerCase().endsWith('.csv')) continue;
const text = await file.text();
let { config, confident } = buildConfig(text);
if (!config) {
alert(`Could not read "${file.name}" as a CSV file.`);
continue;
}
if (!confident) {
try {
config = await showConfigModal(text, config, file.name);
} catch {
continue; // user cancelled
}
}
const parsed = parseGeneric(text, config);
if (!parsed) {
alert(`No data could be read from "${file.name}".`);
continue;
}
const transactionMode = config.balanceCol == null && config.balanceCurrencies == null;
const defaultName = config.name
|| file.name.replace(/\.csv$/i, '').replace(/[_\-]+/g, ' ');
state.accounts.push({
id: nextId++,
name: defaultName,
series: parsed.series.map(s => ({ ...s, enabled: true })),
transactionMode,
rawText: text,
config: config,
initialBalances: transactionMode ? { ...config.initialBalances } : {},
});
}
updateAccountList();
await refreshRates();
render();
}
async function refreshRates() {
const allCurrencies = new Set([state.displayCurrency]);
let minDate = null, maxDate = null;
for (const account of state.accounts) {
for (const s of account.series) {
allCurrencies.add(s.currency);
for (const p of s.points) {
if (!minDate || p.date < minDate) minDate = p.date;
if (!maxDate || p.date > maxDate) maxDate = p.date;
}
}
}
updateFixedRatesPanel();
document.getElementById('rate-note').style.display = 'none';
if (!minDate) return;
if (state.rateSource === 'daily') await ensureRates(Array.from(allCurrencies), minDate, maxDate);
}
// Rebuild the fixed-rates input rows to match currently loaded currencies.
function updateFixedRatesPanel() {
const panel = document.getElementById('fixed-rates-panel');
if (!panel) return;
const isFixed = state.rateSource === 'fixed';
panel.style.display = isFixed ? '' : 'none';
if (!isFixed) return;
const currencies = [...new Set(
state.accounts.flatMap(acc => acc.series.map(s => s.currency))
)].filter(c => c !== 'USD').sort();
const rows = document.getElementById('fixed-rates-rows');
if (!state.perYearMode) {
rows.classList.remove('per-year');
// Preserve existing inputs before re-render
rows.querySelectorAll('.fixed-rate-input').forEach(inp => {
const v = parseFloat(inp.value);
if (!isNaN(v) && v > 0) state.fixedRates[inp.dataset.ccy] = v;
});
rows.innerHTML = currencies.map(ccy => `
<div class="fixed-rate-entry">
<span class="fixed-rate-label">1 USD =</span>
<input type="number" class="fixed-rate-input" step="0.0001" min="0"
data-ccy="${escapeHtml(ccy)}"
value="${state.fixedRates[ccy] != null ? state.fixedRates[ccy] : ''}">
<span class="fixed-rate-ccy">${escapeHtml(ccy)}</span>
</div>
`).join('') || '<span style="font-size:12px;color:#aaa">Load accounts to see currencies</span>';
rows.querySelectorAll('.fixed-rate-input').forEach(inp => {
inp.addEventListener('change', e => {
const v = parseFloat(e.target.value);
if (!isNaN(v) && v > 0) { state.fixedRates[e.target.dataset.ccy] = v; render(); }
});
});
} else {
rows.classList.add('per-year');
// Preserve existing per-year inputs before re-render
rows.querySelectorAll('.per-year-input').forEach(inp => {
const v = parseFloat(inp.value);
if (!isNaN(v) && v > 0) {
if (!state.fixedRatesPerYear[inp.dataset.ccy]) state.fixedRatesPerYear[inp.dataset.ccy] = {};
state.fixedRatesPerYear[inp.dataset.ccy][inp.dataset.year] = v;
}
});
const years = getDataYears();
if (!currencies.length || !years.length) {
rows.innerHTML = '<span style="font-size:12px;color:#aaa">Load accounts to see currencies</span>';
return;
}
const headerCells = years.map(y => `<th class="per-year-th">${y}</th>`).join('');
const bodyRows = currencies.map(ccy => {
const cells = years.map(yr => {
const val = state.fixedRatesPerYear[ccy]?.[yr];
const tRate = treasuryForYear(ccy, yr);
const isTreasury = val != null && tRate != null && Math.abs(val - tRate) < 1e-9;
const badge = isTreasury ? ' <span class="treasury-badge">T</span>' : '';
return `<td class="per-year-td"><input type="number" class="per-year-input" step="0.0001" min="0" data-ccy="${escapeHtml(ccy)}" data-year="${yr}" value="${val != null ? val : ''}">${badge}</td>`;
}).join('');
return `<tr><td class="per-year-ccy-label">1 USD = <strong>${escapeHtml(ccy)}</strong></td>${cells}</tr>`;
}).join('');
rows.innerHTML = `
<label class="per-year-collapse-label"><input type="checkbox" id="per-year-collapse"> Use single rate for all years</label>
<table class="per-year-table">
<thead><tr><th></th>${headerCells}</tr></thead>
<tbody>${bodyRows}</tbody>
</table>`;
rows.querySelector('#per-year-collapse').addEventListener('change', e => {
if (e.target.checked) {
for (const ccy of currencies) {
const byYear = state.fixedRatesPerYear[ccy];
if (!byYear) continue;
const yrs = Object.keys(byYear).sort();
if (yrs.length) state.fixedRates[ccy] = byYear[yrs[yrs.length - 1]];
}
state.perYearMode = false;
updateFixedRatesPanel();
render();
}
});
rows.querySelectorAll('.per-year-input').forEach(inp => {
inp.addEventListener('change', e => {
const v = parseFloat(e.target.value);
if (!isNaN(v) && v > 0) {
const { ccy, year } = e.target.dataset;
if (!state.fixedRatesPerYear[ccy]) state.fixedRatesPerYear[ccy] = {};
state.fixedRatesPerYear[ccy][year] = v;
updateFixedRatesPanel();
render();
}
});
});
}
}
// ─────────────────────────────────────────────────────────────
// Controls
// ─────────────────────────────────────────────────────────────
function setViewMode(mode) {
state.viewMode = mode;
document.getElementById('btn-individual').classList.toggle('active', mode === 'individual');
document.getElementById('btn-sum').classList.toggle('active', mode === 'sum');
render();
}
// ─────────────────────────────────────────────────────────────
// Configuration Modal
// ─────────────────────────────────────────────────────────────
let _modalState = null; // {text, fileName, resolve, reject, multiCurrency}
function showConfigModal(text, config, fileName) {
return new Promise((resolve, reject) => {
_modalState = { text, fileName, resolve, reject, multiCurrency: false };
document.getElementById('modal-title').textContent = `Configure: "${fileName}"`;
document.getElementById('modal-account-name').value =
config.name || fileName.replace(/\.csv$/i, '').replace(/[_\-]+/g, ' ');
document.getElementById('modal-default-ccy').value = config.defaultCurrency || 'EUR';
document.querySelector(`input[name="modal-numfmt"][value="${config.numberFormat || 'standard'}"]`).checked = true;
document.getElementById('modal-date-format').value = config.dateFormat || 'YYYY-MM-DD';
// Set delimiter radio to match detected delimiter (leave on 'auto' since auto-detected)
document.querySelector('input[name="modal-delim"][value="auto"]').checked = true;
document.getElementById('modal-error').textContent = '';
_modalRefreshPreview(config);
document.getElementById('config-modal').style.display = 'flex';
});
}
function _modalGetDelimiter(allLines) {
const checked = document.querySelector('input[name="modal-delim"]:checked');
return (checked && checked.value !== 'auto')
? checked.value
: detectDelimiter(allLines[0] || '');
}
// Re-render the preview table and column role dropdowns.
// preConfig: if provided, use it to pre-populate roles; otherwise auto-detect.
function _modalRefreshPreview(preConfig) {
if (!_modalState) return;
const allLines = _modalState.text.split(/\r\n|\r|\n/).filter(l => l.trim().length > 0);
const delimiter = _modalGetDelimiter(allLines);
const skipRows = detectSkipRows(allLines, delimiter);
const lines = allLines.slice(skipRows);
if (lines.length < 1) return;
const headers = parseLine(lines[0], delimiter).map(h => h.trim());
const previewRows = lines.slice(1, 6);
const allDataRows = lines.slice(1);
// Preview table
const th = headers.map(h => `<th>${escapeHtml(h)}</th>`).join('');
const tb = previewRows.map(row => {
const f = parseLine(row, delimiter);
return '<tr>' + f.map(v => `<td>${escapeHtml(v)}</td>`).join('') + '</tr>';
}).join('');
document.getElementById('modal-preview').innerHTML =
`<div class="modal-preview-scroll"><table class="modal-preview-table">` +
`<thead><tr>${th}</tr></thead><tbody>${tb}</tbody></table></div>`;
// Determine column roles
const roles = new Array(headers.length).fill('');
const detected = detectColumns(headers);
// Auto-enable multi-currency mode only when source/target cols are present AND
// the data actually contains multiple target currencies (i.e. a true multi-currency export).
if (detected.targetAmountCol != null || detected.sourceAmountCol != null) {
_modalState.multiCurrency =
dataHasMultipleCurrencies(allDataRows, detected.targetCurrencyCol, delimiter);
}
// Apply auto-detected roles first
if (detected.dateCol != null) roles[detected.dateCol] = 'date';
if (detected.balanceCol != null) roles[detected.balanceCol] = 'balance';
if (detected.amountCol != null) roles[detected.amountCol] = 'amount';
if (detected.creditCol != null) roles[detected.creditCol] = 'credit';
if (detected.debitCol != null) roles[detected.debitCol] = 'debit';
if (detected.currencyCol != null) roles[detected.currencyCol] = 'currency';
if (detected.accountCol != null) roles[detected.accountCol] = 'account';
if (detected.directionCol != null) roles[detected.directionCol] = 'direction';
if (detected.sourceAmountCol != null) roles[detected.sourceAmountCol] = 'sourceAmount';
if (detected.sourceCurrencyCol != null) roles[detected.sourceCurrencyCol] = 'sourceCurrency';
if (detected.targetAmountCol != null) roles[detected.targetAmountCol] = 'targetAmount';
if (detected.targetCurrencyCol != null) roles[detected.targetCurrencyCol] = 'targetCurrency';
if (detected.balanceCurrencies) detected.balanceCurrencies.forEach(({ col }) => { roles[col] = 'balance'; });
// Override with preConfig if supplied (and indices are still valid)
if (preConfig) {
const set = (col, role) => { if (col != null && col < roles.length) roles[col] = role; };
set(preConfig.dateCol, 'date');
set(preConfig.balanceCol, 'balance');
set(preConfig.amountCol, 'amount');
set(preConfig.creditCol, 'credit');
set(preConfig.debitCol, 'debit');
set(preConfig.currencyCol, 'currency');
set(preConfig.accountCol, 'account');
set(preConfig.directionCol, 'direction');
set(preConfig.sourceAmountCol, 'sourceAmount');
set(preConfig.sourceCurrencyCol,'sourceCurrency');
set(preConfig.targetAmountCol, 'targetAmount');
set(preConfig.targetCurrencyCol,'targetCurrency');
if (preConfig.balanceCurrencies) preConfig.balanceCurrencies.forEach(({ col }) => { set(col, 'balance'); });
if (preConfig.targetAmountCol != null || preConfig.sourceAmountCol != null) _modalState.multiCurrency = true;
}
const roleOptions = _modalState.multiCurrency ? ROLE_OPTIONS_MULTI : ROLE_OPTIONS_BASE;
const validRoleValues = new Set(roleOptions.map(o => o.v));
const rolesHtml = headers.map((h, i) => {
const cur = validRoleValues.has(roles[i]) ? roles[i] : '';
const opts = roleOptions.map(({ v, l }) =>
`<option value="${v}"${cur === v ? ' selected' : ''}>${l}</option>`
).join('');
const label = h.length > 22 ? h.slice(0, 20) + '…' : h;
return `<div class="col-role-row">` +
`<span class="col-role-name" title="${escapeHtml(h)}">${escapeHtml(label)}</span>` +
`<select class="col-role-select" data-col="${i}">${opts}</select>` +
`</div>`;
}).join('');
document.getElementById('modal-col-roles').innerHTML = rolesHtml;
document.getElementById('modal-multiccy-check').checked = _modalState.multiCurrency;
_modalUpdateCcySection();
_modalUpdateDirectionSection();
}
// Rebuild only the column role dropdowns (used when multi-currency toggle changes).
// Preserves current selections where the role remains valid in the new option set.
function _modalRebuildRoles() {
if (!_modalState) return;
const saved = {};
document.querySelectorAll('.col-role-select').forEach(sel => { saved[sel.dataset.col] = sel.value; });
const allLines = _modalState.text.split(/\r\n|\r|\n/).filter(l => l.trim().length > 0);
const delimiter = _modalGetDelimiter(allLines);
const skipRows = detectSkipRows(allLines, delimiter);
const headers = parseLine(allLines[skipRows] || '', delimiter).map(h => h.trim());
const roleOptions = _modalState.multiCurrency ? ROLE_OPTIONS_MULTI : ROLE_OPTIONS_BASE;
const validRoleValues = new Set(roleOptions.map(o => o.v));
const rolesHtml = headers.map((h, i) => {
const cur = validRoleValues.has(saved[String(i)]) ? saved[String(i)] : '';
const opts = roleOptions.map(({ v, l }) =>
`<option value="${v}"${cur === v ? ' selected' : ''}>${l}</option>`
).join('');
const label = h.length > 22 ? h.slice(0, 20) + '…' : h;
return `<div class="col-role-row">` +
`<span class="col-role-name" title="${escapeHtml(h)}">${escapeHtml(label)}</span>` +
`<select class="col-role-select" data-col="${i}">${opts}</select>` +
`</div>`;
}).join('');
document.getElementById('modal-col-roles').innerHTML = rolesHtml;
_modalUpdateCcySection();
_modalUpdateDirectionSection();
}
function _modalUpdateCcySection() {
const hasCcyCol = Array.from(document.querySelectorAll('.col-role-select'))
.some(s => s.value === 'currency');
document.getElementById('modal-ccy-section').style.display = hasCcyCol ? 'none' : '';
}
function _modalUpdateDirectionSection() {
const section = document.getElementById('modal-direction-section');
const mapDiv = document.getElementById('modal-direction-map');
const dirSel = Array.from(document.querySelectorAll('.col-role-select'))
.find(s => s.value === 'direction');
if (!dirSel || !_modalState) { section.style.display = 'none'; return; }
const colIndex = parseInt(dirSel.dataset.col, 10);
const allLines = _modalState.text.split(/\r\n|\r|\n/).filter(l => l.trim().length > 0);
const delimiter = _modalGetDelimiter(allLines);
const skipRows = detectSkipRows(allLines, delimiter);
const dataRows = allLines.slice(skipRows + 1);
const multiCcy = !!( _modalState && _modalState.multiCurrency);
const map = buildDefaultDirectionMap(dataRows, colIndex, delimiter, multiCcy);
// If all values are recognized terms, no need to show mapping UI
if (directionMapIsAutoResolved(map, multiCcy)) { section.style.display = 'none'; return; }
// Preserve any user-modified selections
const existing = {};
mapDiv.querySelectorAll('.dir-map-select').forEach(sel => {
existing[sel.dataset.value] = sel.value;
});
const dirChoices = multiCcy ? ['credit', 'debit', 'both', 'skip'] : ['credit', 'debit', 'skip'];
const values = Object.keys(map).sort();
mapDiv.innerHTML = values.map(v => {
const cur = existing[v] || map[v];
const opts = dirChoices.map(o =>
`<option value="${o}"${cur === o ? ' selected' : ''}>${o.charAt(0).toUpperCase() + o.slice(1)}</option>`
).join('');
return `<div class="dir-map-row">` +
`<span class="dir-map-value">${escapeHtml(v)}</span>` +
`<select class="dir-map-select" data-value="${escapeHtml(v)}">${opts}</select>` +
`</div>`;
}).join('');
section.style.display = '';
}
function _modalImport() {
if (!_modalState) return;
const allLines = _modalState.text.split(/\r\n|\r|\n/).filter(l => l.trim().length > 0);
const delimiter = _modalGetDelimiter(allLines);
const skipRows = detectSkipRows(allLines, delimiter);
const lines = allLines.slice(skipRows);
const headers = parseLine(lines[0] || '', delimiter).map(h => h.trim());
const cols = {
dateCol: null, balanceCol: null, balanceCurrencies: [],
amountCol: null, creditCol: null, debitCol: null,
directionCol: null, currencyCol: null, accountCol: null,
sourceAmountCol: null, sourceCurrencyCol: null, targetAmountCol: null, targetCurrencyCol: null,
};
document.querySelectorAll('.col-role-select').forEach(sel => {
const i = +sel.dataset.col;
switch (sel.value) {
case 'date': cols.dateCol = i; break;
case 'balance': {
const m = (headers[i] || '').match(/^(?:Balance|Saldo) \((\w+)\)$/i);
if (m) cols.balanceCurrencies.push({ col: i, ccy: m[1].toUpperCase() });
else cols.balanceCol = i;
break;
}
case 'amount': cols.amountCol = i; break;
case 'credit': cols.creditCol = i; break;
case 'debit': cols.debitCol = i; break;
case 'direction': cols.directionCol = i; break;
case 'currency': cols.currencyCol = i; break;
case 'account': cols.accountCol = i; break;
case 'sourceAmount': cols.sourceAmountCol = i; break;
case 'sourceCurrency': cols.sourceCurrencyCol = i; break;
case 'targetAmount': cols.targetAmountCol = i; break;
case 'targetCurrency': cols.targetCurrencyCol = i; break;
}
});
if (!cols.balanceCurrencies.length) cols.balanceCurrencies = null;
// Validate
const errEl = document.getElementById('modal-error');
if (cols.dateCol == null) {
errEl.textContent = 'Please assign a Date column.'; return;
}
const hasAmount = cols.amountCol != null || cols.creditCol != null || cols.debitCol != null;
const hasMultiCcy = cols.targetAmountCol != null && cols.sourceAmountCol != null;
if (cols.balanceCol == null && !cols.balanceCurrencies && !hasAmount && !hasMultiCcy) {
errEl.textContent = 'Please assign a Balance, Amount, or Source+Target Amount column.'; return;
}
if (cols.directionCol != null && !hasAmount && !hasMultiCcy) {
errEl.textContent = 'Direction column requires an Amount (or Source/Target Amount) column.'; return;
}
errEl.textContent = '';
const numberFormat = document.querySelector('input[name="modal-numfmt"]:checked').value;
const dateFormat = document.getElementById('modal-date-format').value;
const defaultCcy = (document.getElementById('modal-default-ccy').value.trim().toUpperCase()) || 'EUR';
const accountInput = document.getElementById('modal-account-name').value.trim();
let name = accountInput || null;
if (!name && cols.accountCol != null && lines.length > 1) {
const firstRow = parseLine(lines[1], delimiter);
const acct = (firstRow[cols.accountCol] || '').replace(/\s/g, '');
if (acct.length >= 4) name = acct.slice(-4);
}
const multiCcy = !!(document.getElementById('modal-multiccy-check').checked);
// Build direction map from UI if direction column is assigned
let directionMap = null;
if (cols.directionCol != null) {
directionMap = {};
const mapSelects = document.querySelectorAll('#modal-direction-map .dir-map-select');
if (mapSelects.length > 0) {
// User-configured mapping from the direction section
mapSelects.forEach(sel => { directionMap[sel.dataset.value] = sel.value; });
} else {
// Section was hidden (auto-resolved) — build default map from data
const dataRows = allLines.slice(skipRows + 1);
directionMap = buildDefaultDirectionMap(dataRows, cols.directionCol, delimiter, multiCcy);
}
}
const config = {
delimiter, skipRows,
dateCol: cols.dateCol,
balanceCol: cols.balanceCol,
balanceCurrencies: cols.balanceCurrencies,
amountCol: cols.amountCol,
creditCol: cols.creditCol,
debitCol: cols.debitCol,
directionCol: cols.directionCol,
directionMap,
sourceAmountCol: cols.sourceAmountCol,
sourceCurrencyCol: cols.sourceCurrencyCol,
targetAmountCol: cols.targetAmountCol,
targetCurrencyCol: cols.targetCurrencyCol,
currencyCol: cols.currencyCol,
accountCol: cols.accountCol,
defaultCurrency: defaultCcy,
dateFormat, numberFormat,
initialBalances: {},
name,
};
document.getElementById('config-modal').style.display = 'none';
const { resolve } = _modalState;
_modalState = null;
resolve(config);
}
function _modalCancel() {
document.getElementById('config-modal').style.display = 'none';
if (_modalState) {
const { reject } = _modalState;
_modalState = null;
reject(new Error('cancelled'));
}
}
// ─────────────────────────────────────────────────────────────
// Init
// ─────────────────────────────────────────────────────────────
document.addEventListener('DOMContentLoaded', () => {
document.getElementById('rate-source').value = 'daily';
state.rateSource = 'daily';
const dropzone = document.getElementById('dropzone');
const fileInput = document.getElementById('file-input');
dropzone.addEventListener('click', () => fileInput.click());
dropzone.addEventListener('dragover', e => { e.preventDefault(); dropzone.classList.add('drag-over'); });
dropzone.addEventListener('dragleave', () => dropzone.classList.remove('drag-over'));
dropzone.addEventListener('drop', e => {
e.preventDefault();
dropzone.classList.remove('drag-over');
processFiles(Array.from(e.dataTransfer.files));
});
fileInput.addEventListener('change', e => {
processFiles(Array.from(e.target.files));
fileInput.value = '';
});
document.getElementById('display-currency').addEventListener('change', async e => {
state.displayCurrency = e.target.value;
await refreshRates();
render();
});
document.getElementById('rate-source').addEventListener('change', async e => {
state.rateSource = e.target.value;
setStatus('', '');
document.getElementById('api-warning').classList.remove('visible');
await refreshRates();
render();
});
document.getElementById('btn-fetch-fbar').addEventListener('click', async () => {
const currencies = new Set();
let minDate = null, maxDate = null;
for (const acc of state.accounts)
for (const s of acc.series) {
currencies.add(s.currency);
for (const p of s.points) {
if (!minDate || p.date < minDate) minDate = p.date;
if (!maxDate || p.date > maxDate) maxDate = p.date;
}
}
currencies.delete('USD');
if (!currencies.size || !minDate) return;
const statusEl = document.getElementById('fbar-status');
statusEl.textContent = 'Fetching…';
await ensureTreasuryRates(Array.from(currencies), minDate, maxDate);
// Populate per-year rates (forward-fill treasury for each data year)
state.perYearMode = true;
const years = getDataYears();
for (const ccy of currencies) {
if (!state.fixedRatesPerYear[ccy]) state.fixedRatesPerYear[ccy] = {};
for (const yr of years) {
const rate = treasuryForYear(ccy, yr);
if (rate !== null) state.fixedRatesPerYear[ccy][yr] = rate;
}
// Also keep fixedRates updated with the most recent year (collapse fallback)
const rate = treasuryForYear(ccy, years[years.length - 1]);
if (rate !== null) state.fixedRates[ccy] = rate;
}
updateFixedRatesPanel();
render();
const anyFound = [...currencies].some(c => Object.keys(state.fixedRatesPerYear[c] || {}).length);
statusEl.textContent = anyFound
? `Treasury year-end rates loaded (${years.join(', ')})`
: 'No rates found';
});
// Modal events
document.getElementById('modal-import').addEventListener('click', _modalImport);
document.getElementById('modal-cancel').addEventListener('click', _modalCancel);
document.getElementById('modal-cancel-x').addEventListener('click', _modalCancel);
document.getElementById('config-modal').addEventListener('click', e => {
if (e.target === document.getElementById('config-modal')) _modalCancel();
});
document.querySelectorAll('input[name="modal-delim"]').forEach(r =>
r.addEventListener('change', () => _modalRefreshPreview(null))
);
document.getElementById('modal-col-roles').addEventListener('change', e => {
if (!e.target.classList.contains('col-role-select')) return;
_modalUpdateCcySection();
_modalUpdateDirectionSection();
if (e.target.value === 'date') {
const colIndex = parseInt(e.target.dataset.col, 10);
const allLines = _modalState.text.split(/\r\n|\r|\n/).filter(l => l.trim().length > 0);
const delimiter = _modalGetDelimiter(allLines);
const skipRows = detectSkipRows(allLines, delimiter);
const dataRows = allLines.slice(skipRows + 1);
const fmt = detectDateFormat(dataRows, colIndex, delimiter);
document.getElementById('modal-date-format').value = fmt;
}
});
document.getElementById('col-roles-info').addEventListener('click', () => {
const help = document.getElementById('col-roles-help');
help.style.display = help.style.display === 'none' ? '' : 'none';
});
document.getElementById('modal-multiccy-check').addEventListener('change', e => {
if (_modalState) {
_modalState.multiCurrency = e.target.checked;
_modalRebuildRoles();
}
});
});
</script>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment