[Fast Investment Analysis — One-Click Trading Report Generation]

【Functionality】: Rapid investment analysis is divided into four modules: transaction details analysis, transaction summary statistics, bank-securities transfer, and annualized return rate. Each sub-module contains detailed data analysis reports.
【Features】:
A. Generate all four sub-modules with one click. Automatically captures the latest stock prices via Teng* interface, combining net capital inflow and existing shares. The transaction details analysis module calculates reference profit and loss data consistent with trading software. This code set exemplifies high-performance VBA programming, utilizing multi-array operations, multiple dictionary aggregations, batch writing, and other techniques to process tens of thousands of transaction records in seconds.
B. For complex business types in settlement slips, such as “bond coupon interest and tax deduction,” “dividend credited,” “fund dividend allocation,” “ETF cash difference refund,” “fund subscription,” “additional holdings,” “securities repurchase,” “bonus shares credited,” “allotment payment,” “share transfer in,” “subscription winning,” “dividend tax difference,” “LOF subscription,” “bond coupon interest and tax deduction,” “trust transfer in,” “Shanghai Stock Exchange LOF subscription,” “OTC fund outflow,” “redemption,” “reduction,” “securities repurchase,” “bond coupon interest,” “dividend credited,” “fund dividend allocation,” “subscription refund,” “cash difference refund,” “interest principal repayment,” “OTC fund inflow,” etc., which involve repeated calculations of transaction volume and amounts, the program can automatically recognize and perform related calculations to ensure the accuracy of analysis reports.
【Usage】:
A. Copy the historical settlement slip data from Galaxy Securities (downloadable from the settlement slip section in the trading software) into the “Settlement Slip” worksheet starting from the second row (no need to copy the header).
B. In the “Investment Analysis” worksheet, click the “Generate Report” button to automatically produce the investment analysis report.
【Notes】:
A. The code is only applicable to Galaxy Securities (since I have been using Galaxy Securities trading, the code is written based on Galaxy Securities’ settlement slip field structure). Other securities’ settlement slips may not be compatible due to different field structures—please pay special attention.
B. Additionally, the security code is a unique identifier key. When dealing with IPO codes (stocks, REITs, convertible bonds) that differ from the listed code, you need to manually edit the settlement slip to change the IPO code to the listed code; otherwise, they will be treated as separate securities in the statistics.
C. For options accounts involving exercise, when selling puts, the stock account will have additional ETF shares after exercise, but the amount on the stock settlement slip will be zero (which is equivalent to buying ETFs; you need to manually input the exercised amount in the transaction amount). Also, the funds required for exercising options are not listed on the stock settlement slip (which is equivalent to fund transfer-in, so manually add the transferred-in funds). When selling or buying to exercise, similar manual editing is needed.
【Access Method】: It’s not easy to code, but if you find it useful, feel free to tip or share via private message.