Some Background
Although I’ve already developed options-related pages with Bolt, in actual trading I still prefer using Google Sheets to organize options data, mainly for the following reasons:
- The backend system isn’t yet connected to QMT, so data can only be imported manually — there’s no automation advantage.
- My options trading is still at a fairly novice stage, still exploring suitable tools; researching display pages at this point seems low-yield to me
- Google Sheets’ interface is very friendly — data viewing and editing are convenient, and I can gradually sort out what I actually want
- I also tried Feishu’s docs; I couldn’t understand its custom function support. Considering practicality and extensibility, I chose Google Sheets
My Google Sheet address is below; if you want to modify it, readers can copy it directly into their own Google account and modify it:
Getting Option Holdings
I use Guojin’s Jintaiyang to view my holdings — a customized version of Tongdaxin that also provides data export.
I use its export function to export to Excel, then copy into Google Sheets:
Organizing Holding Returns
I want to record my position changes as much as possible — conveniently recorded and viewed with Google Sheets and charts:
Organizing the Google Sheet (built-in functions)
In this image, several parts are implemented with Google Sheets’ built-in functions.
Filtering by Expiration Date
For example, in the whole tab I first filter by the expiration date corresponding to cell Q2 — here 20251022 — then sort by contract name:
1 | =sort(filter('持仓'!A2:N, '持仓'!N2:N = Q2), 2, true) |
Getting Open CALLs
Then I use this formula to get open CALLs, as follows. Its rules are:
- When I find the option name contains the character
购(call) – it’s a call option - Available quantity greater than 0 – meaning there are still open positions
- Opening price negative – meaning it was being sold at the time
- And not covered – non-covered
1 | =FILTER(B2:O20, regexmatch(C2:C20,".*购.*"), F2:F20>0, G2:G20<0, NOT(D2:D20="备兑")) |
Open PUTs are similar; readers can check the formulas in the sheet themselves.
Calling Custom Functions
For these short positions with possible exercise risk, I also wrote some custom functions to help me with calculations — e.g. the current underlying price, computing whether a contract is in or out of the money.
Writing the Function Code
You can open this location to view the script:
The code inside I asked Gemini to write — far faster than writing it myself, with comments too. Thumbs up!
Calling Functions in the Sheet
I can directly call the written functions to compute all options’ statuses, making it easy to assess risk — which in-the-money short positions need closing or rolling before expiration:
1 | =ANALYZE_OPTION_STATUS(C:C, R2*1000) |
Summary
Honestly, neither using my own program nor Google Sheets is especially convenient. But having used broker software including Tongdaxin and Huidian, their analysis of already-held option positions is very limited — you can only see basic information. There isn’t even classification by month; you only see a rough holding. As I trade more and more options, this information becomes quite insufficient.
Institutional traders presumably have their own systems to manage this data, but for individual investors I haven’t yet found a particularly good tool. If you have recommended options tools, please leave a comment and tell me.