From 12c4d3625458c830a2746a1f277fb684e9c498cb Mon Sep 17 00:00:00 2001 From: "Somhairle H. Marisol" Date: Mon, 21 Sep 2026 22:43:26 +0800 Subject: Add returns and charts slice (3d-9b) --- src/FundLab.Api/Persistence.fs | 229 ++++++++++++++++++++++++++++++++++++++++- 1 file changed, 228 insertions(+), 1 deletion(-) (limited to 'src/FundLab.Api/Persistence.fs') diff --git a/src/FundLab.Api/Persistence.fs b/src/FundLab.Api/Persistence.fs index 973544f..a8abd94 100644 --- a/src/FundLab.Api/Persistence.fs +++ b/src/FundLab.Api/Persistence.fs @@ -142,6 +142,7 @@ type FundRecord = AvailableCash: decimal ReservedCash: decimal Status: string + CreatedAt: DateTimeOffset } type FundWriteResult = @@ -433,6 +434,30 @@ type CapitalDepositWriteResult = | CapitalDepositInvalid of string | CapitalDepositFundNotFound +/// One reconstructed end-of-day fund valuation. `TotalAssets`/`UnitNav`/ +/// `HoldingsValue`/`CumulativeReturn` are `None` when a held instrument has no +/// NAV observation dated on or before `Date`: the value is unknown, never zero. +type FundReturnsPoint = + { + Date: DateOnly + Pending: bool + TotalAssets: decimal option + UnitNav: decimal option + Cash: decimal + ReservedCash: decimal + HoldingsValue: decimal option + CumulativeReturn: decimal option + NetExternalFlow: decimal + } + +type FundReturns = + { + FundId: Guid + Pending: bool + DataUpdatedAt: DateTimeOffset option + Points: FundReturnsPoint list + } + type FundRepository(connectionString: string) = let cashMaximum = 999999999999999999.99m let unitNavMaximum = 99999999999999999999.99999999m @@ -731,6 +756,7 @@ type FundRepository(connectionString: string) = AvailableCash = fund.AvailableCash ReservedCash = fund.FrozenCash Status = statusText fund.Status + CreatedAt = DateTimeOffset.UtcNow } let recordFromReader (reader: DbDataReader) = @@ -744,6 +770,7 @@ type FundRepository(connectionString: string) = AvailableCash = reader.GetDecimal(6) ReservedCash = reader.GetDecimal(7) Status = reader.GetString(8) + CreatedAt = reader.GetFieldValue(9) } let dateTimeOffsetFromReader (reader: DbDataReader) index = @@ -811,7 +838,7 @@ type FundRepository(connectionString: string) = transaction """ SELECT id, name, currency, initial_cash, initial_unit_nav, - is_synthetic, available_cash, reserved_cash, status + is_synthetic, available_cash, reserved_cash, status, created_at FROM funds WHERE id = @fund_id """ @@ -4043,3 +4070,203 @@ type FundRepository(connectionString: string) = ) records |> Seq.toList + + /// Reconstructs the fund's asset and unit-NAV history from confirmed ledger + /// events plus persisted NAV observations. Unit NAV is rebuilt by issuing fund + /// units for external capital deposits at the previously known unit NAV, so a + /// deposit never moves the unit NAV. Values that cannot be known at a date stay + /// `None`; nothing is filled with zero. + member this.GetFundReturns(fundId: Guid) : FundReturns option = + match this.GetFund fundId with + | None -> None + | Some fund -> + let today = ConfirmationPolicy.shanghaiDate DateTimeOffset.UtcNow + let deposits = this.GetCapitalDeposits fundId + let orders = this.GetSubscriptionOrders fundId + let redemptions = this.GetRedemptionOrders fundId + let dividends = this.GetDividendRecords fundId + + let heldCodes = + [ yield! orders |> List.map (fun order -> order.FundCode) + yield! redemptions |> List.map (fun order -> order.InstrumentCode) ] + |> List.distinct + + let navByCode = + heldCodes + |> List.map (fun code -> code, this.GetNav(code, None, Some today)) + |> Map.ofList + + let firstHoldingDate (code: string) = + orders + |> List.filter (fun order -> order.FundCode = code && order.Status = "confirmed") + |> List.map (fun order -> order.TradeDate) + |> List.sort + |> List.tryHead + + let navDates = + heldCodes + |> List.collect (fun code -> + match firstHoldingDate code with + | None -> [] + | Some startDate -> + navByCode.[code] + |> List.filter (fun observation -> + observation.NavDate >= startDate && observation.NavDate <= today) + |> List.map (fun observation -> observation.NavDate)) + |> Set.ofList + + let creationDate = ConfirmationPolicy.shanghaiDate fund.CreatedAt + + let dividendCredited = + dividends + |> List.filter (fun record -> record.Status = "cash_credited" || record.Status = "succeeded") + + let hasActivity = + not (List.isEmpty deposits) + || not (List.isEmpty orders) + || not (List.isEmpty redemptions) + || not (List.isEmpty dividendCredited) + || not (Set.isEmpty navDates) + + if not hasActivity then + Some + { + FundId = fundId + Pending = false + DataUpdatedAt = None + Points = [] + } + else + let eventDates = + [ yield creationDate + yield! deposits |> List.map (fun deposit -> ConfirmationPolicy.shanghaiDate deposit.CreatedAt) + yield! orders |> List.map (fun order -> order.TradeDate) + yield! redemptions |> List.map (fun order -> order.TradeDate) + yield! dividendCredited |> List.map (fun record -> record.NavDate) ] + |> Set.ofList + + let dates = + Set.union eventDates navDates + |> Set.toList + |> List.sort + + let dataUpdatedAt = + navByCode + |> Map.toList + |> List.collect snd + |> List.map (fun observation -> observation.LastSeenAt) + |> List.sortDescending + |> List.tryHead + + let latestNavOnOrBefore (code: string) (date: DateOnly) = + match Map.tryFind code navByCode with + | None -> None + | Some observations -> + observations + |> List.filter (fun observation -> observation.NavDate <= date && observation.Nav > 0m) + |> List.sortByDescending (fun observation -> observation.NavDate, observation.SourceCollectedAt) + |> List.tryHead + + let mutable availableCash = fund.InitialCash + let mutable reservedCash = 0m + let mutable fundUnits = if fund.InitialUnitNav > 0m then fund.InitialCash / fund.InitialUnitNav else 0m + let mutable lastKnownNav = if fund.InitialUnitNav > 0m then Some fund.InitialUnitNav else None + let positions = System.Collections.Generic.Dictionary() + let mutable cumulativeDeposits = 0m + let points = ResizeArray() + + for date in dates do + for deposit in deposits do + if ConfirmationPolicy.shanghaiDate deposit.CreatedAt = date then + availableCash <- availableCash + deposit.Amount + cumulativeDeposits <- cumulativeDeposits + deposit.Amount + + match lastKnownNav with + | Some nav when nav > 0m -> fundUnits <- fundUnits + deposit.Amount / nav + | _ -> () + + for order in orders do + if order.TradeDate = date then + if order.Status = "confirmed" then + let residual = order.ConfirmedResidualCash |> Option.defaultValue 0m + availableCash <- availableCash + residual - order.ReservedTotal + + let units = order.ConfirmedUnits |> Option.defaultValue 0m + let current = + match positions.TryGetValue order.FundCode with + | true, value -> value + | _ -> 0m + + positions.[order.FundCode] <- current + units + else + availableCash <- availableCash - order.ReservedTotal + reservedCash <- reservedCash + order.ReservedTotal + + for order in redemptions do + if order.TradeDate = date && order.Status = "confirmed" then + let current = + match positions.TryGetValue order.InstrumentCode with + | true, value -> value + | _ -> 0m + + positions.[order.InstrumentCode] <- current - order.Units + availableCash <- availableCash + (order.ConfirmedProceeds |> Option.defaultValue 0m) + + for record in dividendCredited do + if record.NavDate = date then + availableCash <- availableCash + (record.GrossCash |> Option.defaultValue 0m) + + let mutable pending = false + let mutable holdingsValue = 0m + + for KeyValue(code, units) in positions do + if units > 0m then + match latestNavOnOrBefore code date with + | Some observation -> holdingsValue <- holdingsValue + units * observation.Nav + | None -> pending <- true + + let netExternalFlow = fund.InitialCash + cumulativeDeposits + + if pending then + points.Add( + { + Date = date + Pending = true + TotalAssets = None + UnitNav = None + Cash = availableCash + ReservedCash = reservedCash + HoldingsValue = None + CumulativeReturn = None + NetExternalFlow = netExternalFlow + } + ) + else + let totalAssets = availableCash + reservedCash + holdingsValue + let unitNav = if fundUnits > 0m then Some(totalAssets / fundUnits) else None + + match unitNav with + | Some nav -> lastKnownNav <- Some nav + | None -> () + + points.Add( + { + Date = date + Pending = false + TotalAssets = Some totalAssets + UnitNav = unitNav + Cash = availableCash + ReservedCash = reservedCash + HoldingsValue = Some holdingsValue + CumulativeReturn = Some(totalAssets - netExternalFlow) + NetExternalFlow = netExternalFlow + } + ) + + Some + { + FundId = fundId + Pending = points |> Seq.exists (fun point -> point.Pending) + DataUpdatedAt = dataUpdatedAt + Points = points |> Seq.toList + } -- cgit v1.2.3