Data, calculations and reports
Formulas, spreadsheet exports and database work.
Calculations and formulas
Calculate max drawdown for a stock
We need to calculate the max drawdown for a selected stock over a selected period. Show it in the protect section, under the max stock drawdown label (see attached screenshot).
Use the main stock data from the first strategy bucket. If that bucket holds more than one stock, add a drop-down next to the max stock drawdown label.
Use the reporting period. Make sure the calculation is efficient and stays fast even for long periods.
Definition: max drawdown is the maximum of (mv(a) - mv(b)) / mv(a), where a < b are days in the period and mv is the stock's closing price on that day.
To show that you understood the task, include several worked examples of the drawdown in the exit report, with stock charts that highlight the drawdown.
Size: TaskStart to finish: 1 h 24 minAgent work: 1 h 18 minQuestions asked: 3Tokens: 85.3MCost, list price: $54.56Lines added / deleted: +364 −17Pull requests: 1
Propose a formula for overall diversified performance
Suggest a formula to measure "overall diversified performance": the performance of all of the account holder's investments. It should include buckets 1, 2 and 3:
- For buckets 1 and 2, use the time-weighted return that is already calculated.
- For bucket 3, you can use the total market value.
Illustrate the calculation with several examples using real data from the system.
Don't change the code; just prepare the report.
Size: TaskStart to finish: 30 h 18 minAgent work: 3 h 48 minQuestions asked: 8Tokens: 40.8MCost, list price: $36.62
Calculate security weight in a bucket
As part of the portfolio report we have implemented the aggregation of securities into strategy buckets. Now we need a function that calculates the weight of each security in a given bucket on a given date. The weight is the security's market value (position quantity on that date times price) divided by the market value of the entire bucket on that date. The bucket's market value is the sum of all constituent securities' market values for that date.
The quantity can be looked up in the position table (filter by the as-of date and account columns). The closing price is also in the position table (price column).
To ensure accuracy, implement this unit test:
Use case: weight of Stock A is calculated. An account has Stock A, Stock B and Stock C in bucket 1. Position records for Jan 5th, 2026:
- Stock A: quantity 10, price $200
- Stock B: quantity 20, price $300
- Stock C: quantity 15, price $400
Bucket market value = 20010 + 30020 + 400*15 = $14,000. Stock A market value = $2,000. Weight of Stock A = 2000/14000 = 0.1428571429.
Size: TaskStart to finish: 19 h 48 minAgent work: 1 h 36 minQuestions asked: 4Tokens: 11.2MCost, list price: $11.24Lines added / deleted: +326 −3Pull requests: 1
Calculate and display upside participation rate
On the portfolio report, the holdings section shows an Upside Participation Rate for the selected stock. Implement the calculation and display it in the UI.
The upside participation rate shows how much of the stock's upside the customer captured by participating in the strategy. It compares the time-weighted return of the stock alone with the time-weighted return of the stock plus all of its options in bucket 1.
Example: the user selects a stock, and bucket 1 holds two options on it.
- Calculate the stock's return with the existing stock time-weighted return formula, e.g. 20%.
- Calculate the return of the stock plus the two options with the existing stock+option time-weighted return formula, e.g. 15%.
- Upside participation rate = 0.15 / 0.20 = 75%. That's what the user should see.
Show it for the stock selected in the dropdown. If Total is selected, it doesn't need to be shown.
If anything goes wrong in the calculation, show N/A with a tooltip explaining the issue.
Keep the code clean, efficient and reusable. Don't implement any new time-weighted return formulas; reuse the existing ones.
Size: TaskStart to finish: 36 minAgent work: 32 minQuestions asked: 4Tokens: 33.2MCost, list price: $21.02Lines added / deleted: +634 −39Pull requests: 1
Add selectable TWR formulas to report
In the portfolio report we use a time-weighted return formula for each strategy bucket's performance. The existing formula may not be optimal, so add two more TWR formulas and let the user switch between them in the UI. The existing one will be titled "Weights as of {date}" (the period's start date, when the weights are calculated).
Formula 1, "Daily Weight Adjustment": reweight securities daily. E.g. a bucket holds a stock plus a call on it (+10% that day, $12,000) and another stock (+25%, $50,000): the day's bucket TWR is 0.1 * (12000/62000) + 0.25 * (50000/62000). Add 1 to each daily TWR and multiply them all for the period, then subtract 1 for the UI (e.g. +10.25%). A security bought after the start date is picked up from its acquisition date.
Formula 2, "Bucket Market Value": daily return = (today's bucket market value - yesterday's + cash flow) / yesterday's. Factor = return + 1; bucket TWR = (product of factors - 1) * 100.
In the Summary section, left of the bucket drop-down, add a gear icon to pick one of the three formulas. Changing it recalculates the whole report, including the chart and the Excel export.
Keep the code clean and reusable. Don't delete the existing TWR logic.
Size: TaskStart to finish: 1 h 27 minAgent work: 1 h 6 minQuestions asked: 5Tokens: 125.8MCost, list price: $77.21Lines added / deleted: +2,433 −175Pull requests: 1
Implement net value TWR calculation
In the portfolio report, implement the TWR calculation for when the Net Value option is selected in the UI. First, the TWR tile should no longer be blurred when Net Value is selected. Second, the backend must calculate and return the value using the formula below.
Calculate Net Value TWR for each day in the selected period. Take the sum of the market values of all strategy buckets, subtract that day's margin balance positions' market value (this logic already exists), and compare the difference to the previous day's market value. That gives the performance for each day. Finally, multiply all the daily performances together to get the Net Value TWR for the period, and show this value in the UI.
Use the calculations in the attached Excel file to write a unit test that checks the logic is accurate.
Keep the code efficient, clean and reusable.
Size: TaskStart to finish: 1 h 14 minAgent work: 55 minQuestions asked: 3Tokens: 102.7MCost, list price: $63.37Lines added / deleted: +1,576 −159Pull requests: 1
Spreadsheet exports and report audits
Export participation rate calculations to Excel
On the portfolio report, the calculations behind the Upside Participation Rate must be added to the Excel file generated by the Download Excel button. The file needs a separate sheet titled Participation Rate. On it, for each stock in the first strategy bucket, there should be a section showing the calculations behind its upside participation rate. Sections flow horizontally, with an empty column between neighbouring sections.
Each section has a title with the symbols of the stock and the options used in the calculation. Under the title, these columns:
- Stock TWR: the time-weighted return of the stock
- Stock + options TWR: the time-weighted return of the stock and its options for the given date
Rows correspond to the dates the report was generated for in the UI. The last row shows the upside participation rate that was shown in the UI.
Size: TaskStart to finish: 47 minAgent work: 22 minQuestions asked: 4Tokens: 23.6MCost, list price: $15.48Lines added / deleted: +856 −42Pull requests: 1
Report hard-coded values in the portfolio report
Don't make any changes as part of this task. I only need a report in the Artifacts.
Compile a report listing every value on the portfolio report UI that isn't pulled from the backend and is instead hard-coded to a default value in the UI.
Again, don't write any code. Just produce a report.
Size: TaskStart to finish: 21 minAgent work: 8 minQuestions asked: 4Tokens: 4.4MCost, list price: $4.80
Add an Unrealized G/L sheet to the Excel export
The report UI has a parameter called Unrealized Gains/Losses. Add the calculations behind it to the generated Excel file, in a new sheet called "Unrealized G/L", with these columns:
- Security: the symbol of the position's security.
- As of Date: the date the position snapshot was loaded for.
- Unrealized Gain/Loss: the position's unrealized gain or loss on that date.
After the last row, show the total Unrealized Gains/Losses: exactly the same figure as the UI shows.
Size: TaskStart to finish: 1 h 50 minAgent work: 51 minQuestions asked: 8Tokens: 78.3MCost, list price: $48.37Lines added / deleted: +885 −16Pull requests: 1
Add start and end quotes to the drawdown sheet
In the portfolio report's Excel export, add two new columns to the Max Drop sheet:
- Start Date Quote: the quote of the stock or the portfolio on the start date. Put it after the Start Date column.
- End Date Quote: the quote of the stock or the portfolio on the end date. Put it after the End Date column.
Size: TaskStart to finish: 38 minAgent work: 23 minQuestions asked: 5Tokens: 14.6MCost, list price: $10.76Lines added / deleted: +380 −63Pull requests: 1
Check the report's Excel export against its figures
In the portfolio report, the user can generate an Excel file to troubleshoot the calculations. Investigate whether every figure in the report appears in the Excel file, and whether each figure has matching calculations there, so the user can see exactly how the backend arrives at it.
Do not write any code. Just produce the report.
(see attached file)
Size: TaskStart to finish: 13 minAgent work: 8 minQuestions asked: 5Tokens: 14.1MCost, list price: $9.78
Database operations
Shrink the dev seed database dump
The current seed database dump is over 500 MB, which takes a long time to load into worktrees. We need to make it more efficient.
Analyze the current dev database:
- Can we pull it into a local worktree and delete some of the data, making it smaller without sacrificing important data that should be used for performance optimization (a few rows will obviously run fast)?
- Store the dump as a zip and unzip it when the local stack starts
Suggest options first, then implement.
Size: TaskStart to finish: 2 h 39 minAgent work: 1 h 34 minQuestions asked: 6Tokens: 37.1MCost, list price: $31.45Lines added / deleted: +468 −10Pull requests: 1
Restore a production dump into the development databases
We imported several new types of transactions into the production database, and now we need them on the development databases too. Create a dump of the production database and restore it into the develop database, then restore the same dump into the second development database.
Size: TaskStart to finish: 1 h 54 minAgent work: 1 h 45 minQuestions asked: 4Tokens: 16.5MCost, list price: $13.07