ONLYOFFICE Stock/Fund Tracking — "Rufus Retirement Account Tracking 2026.xlsx"

Last Updated: 2026-07-21

🧩 Overview

This workbook tracks live and historical prices for ~50 mutual funds (HSA investment choices) plus a smaller set of regular equities (Dashboard tab), replacing a prior Google Sheets GOOGLEFINANCE()-based workflow. Since ONLYOFFICE has no equivalent live-data formula, price data is supplied via ONLYOFFICE custom functions (JavaScript, run in the browser) — some calling Finnhub directly, some calling the self-hosted STOCKPROXY service (see server_stockproxy.md).

📑 Tabs Involved

Tab	Purpose
Dashboard	Regular equities — live price via Finnhub (`STOCKPROXY` not needed)
HSA Investiments Choices	~50 mutual funds — current + historical price, ranked by return

🔑 Key Cells — "HSA Investiments Choices" tab

Cell	Contents
`B1`	Prior-year marker date
`B2`	`=TODAY()`
`B3`	Resolved calc/anchor date — rolling lookback (currently ~11 months back), computed from B1/B2/B4, with manual override via B12 ("Yes"/"No") + B13
`B4`	Number of lookback months (currently 11)
`B12` / `B13`	Manual override toggle + override date (currently unused, set to "No")

Wide ticker table: rows for Rate, $ Increase, Price (current), Price History (anchor-date price), Fund, Ticker, Rating — cascaded across ~50 columns, one per fund.

Ranking table (top of sheet): Rank / $ Increase / Ticker / Fund / Price / Rating.

🧮 Custom Functions in Use

Full code archived separately — see:

onlyoffice_macro_stockprice_finnhub.js — STOCKPRICE(ticker) / STOCKTIME(ticker)
onlyoffice_macro_fundprice_proxy.js — FUNDPRICE(ticker) / FUNDPRICE_HIST(ticker, dateStr)
onlyoffice_macro_autostart_recalc.js — delayed full-recalc Autostart macro (partial fix, see Known Issues)

Function	Data Source	Used On	Notes
`STOCKPRICE(ticker)`	Finnhub (direct)	Dashboard	Live quote — equities only, not mutual funds
`STOCKTIME(ticker)`	Finnhub (direct)	Dashboard	Exchange-reported quote timestamp
`FUNDPRICE(ticker)`	STOCKPROXY `/current`	HSA Investiments Choices	Current mutual fund price
`FUNDPRICE_HIST(ticker, dateStr)`	STOCKPROXY `/historical`	HSA Investiments Choices	Historical price on/before a date; `dateStr` must be passed as text via `TEXT($B$3,"yyyy-mm-dd")`

Both custom function sets require their own placeholder credentials to be filled in (Finnhub API key; STOCKPROXY shared secret) — real values live in Vaultwarden, never committed to these docs in plaintext.

To recreate: View → Macros → Custom Functions → +, paste the relevant file's contents, replace the placeholder credentials, Save.

⚠️ Known Issues (status as of last session)

1. `#NAME?` / `#BUSY` on file open — UNRESOLVED

Custom function cells show errors on open until manually clicked into + Enter pressed.

Tried: Autostart macro (onlyoffice_macro_autostart_recalc.js) forcing Api.RecalculateAllFormulas() 3 seconds after open.
Result: Only partially effective — a screenshot showed 2 of 34 Price cells resolved correctly after the fix, the rest stayed #NAME?, while the Price History column resolved for all rows. Asymmetry between the two columns not yet explained.
Next steps if resuming: confirm exact formula text in a failing cell (rule out typos from copy-paste across 50 columns); investigate what a user-added "Refresh Sheet" button (seen in a screenshot, cause/origin not yet confirmed with user) does — may indicate a separate macro-poke mechanism exists alongside the live formulas; consider longer Autostart delay or investigate whether #NAME? is a parse-time error that a value-only recalculation cannot fix (may require literally re-entering formula text, not just recalculating).

2. Ctrl+D fill-down not supported — CONFIRMED, NOT A BUG

Longstanding ONLYOFFICE feature gap (multiple open feature requests upstream). Workarounds: drag the fill handle; copy/paste; or install the community "onlyoffice-fill-down" plugin (github.com/une4s/onlyoffice-fill-down) — not yet installed.

3. Conditional formatting + formula cells crashes ONLYOFFICE — CONFIRMED KNOWN BUG

Multiple open upstream GitHub issues (including one from May 2026) describe the editor crashing/erroring when applying or editing conditional formatting on cells containing formulas. This is the issue that triggered the Collabora comparison project below.

4. Silent data loss / revert to hours-old version — CONFIRMED SERIOUS, ONGOING RISK

Long-documented, recurring ONLYOFFICE↔Nextcloud bug class: document silently reverts to an old saved state, sometimes losing hours of work, typically linked to stale browser tabs/sessions or autosave/connectivity edge cases. Not a one-off — confirmed via multiple upstream bug reports spanning years, including as recently as June 2026.

Mitigations discussed (not all yet implemented):

Check Nextcloud's file Versions tab to recover lost work when this happens
Enable "Keep intermediate versions when editing" (forcesave) in the Nextcloud ONLYOFFICE admin settings — creates more frequent real restore points
Always close other tabs/sessions of the same file before editing
Watch for the "all changes saved" indicator before navigating away

🔀 Collabora Online Side-by-Side Comparison — IN PROGRESS

Decision: Rather than pick ONLYOFFICE vs. Collabora based on reputation, run both against the same tracking logic and compare reliability directly. Explicitly not a factor: the Nextcloud/ONLYOFFICE corporate/licensing dispute (Euro-Office fork, partnership suspension, March 2026) — user stated reliability is the only criterion.

Status: setup not yet started — next step when resuming is deploying the Collabora Docker container.

Roadmap

Deploy Collabora Online (CODE) as a Docker container — recommended on NAS16 (not NAS08) to avoid resource contention with Nextcloud/ONLYOFFICE/Pi-hole/Vaultwarden/ STOCKPROXY already running there.
Expose via NPM with its own subdomain (e.g. collabora.kingdezigns.com), SSL via Let's Encrypt — same pattern as stocks.kingdezigns.com.
Install the Collabora Nextcloud connector app (richdocuments) alongside the existing ONLYOFFICE app. Confirmed both can coexist — default double-click opens whichever app is set as default; right-click → "Open with" picks the other.
Make a separate copy of the tracking spreadsheet for Collabora — do not share one live file between both rendering engines simultaneously.
Rebuild FUNDPRICE/FUNDPRICE_HIST logic as LibreOffice Basic or Python macros (Collabora's macro system is entirely different from ONLYOFFICE's JS-based one — full rewrite of the glue code required). STOCKPROXY itself needs no changes — it's a plain HTTPS/JSON API, callable identically from either editor.
Recreate the same conditional formatting that crashes ONLYOFFICE and specifically stress-test that exact scenario on Collabora.
Also re-test on Collabora: file-open recalculation behavior, and general session/reliability behavior (the data-loss concern above).

🧠 Summary for AI Systems

This workbook uses ONLYOFFICE custom functions (not native formulas) to pull live/ historical prices, since ONLYOFFICE has no STOCKHISTORY()-equivalent.
Two data paths: Finnhub direct (equities, Dashboard tab) and STOCKPROXY (mutual funds, HSA tab) — see server_stockproxy.md for the backend service.
Several confirmed ONLYOFFICE reliability issues are actively being weighed against Collabora Online as an alternative — see the Collabora section above for current status.
Real credentials (Finnhub API key, STOCKPROXY shared secret) are never stored in these docs — Vaultwarden is the source of truth, matching the rest of the KingDezigns documentation convention.

8 KiB Raw Permalink Blame History