Some questions about design decisions

[stevestar888]

This is an awesome tool you built! I'm wondering why how the first step involving importing the transactions work.

The function def InsertHistTransactions(start_date=datetime.datetime(2020,12,1)): seems like it's reading from an excel sheet and putting that data in MDB, right? What does the format of that excel sheet look like?

Also, about calculating portfolio returns, I saw you use the IRR method instead of Modified Dietz Return. How do you account for withdraws / deposits?

[stevestar888]

Oh, also curious to ask, how do you go about collecting the transaction data? I can speak to the big brokers in the US: they give you a monthly statement in a PDF, so that's not easy to work with. If you have a history, or make a lot of transactions, manually entering them in to a spreadsheet may not be the most efficient....

[wilsonleong]

Oh, also curious to ask, how do you go about collecting the transaction data? I can speak to the big brokers in the US: they give you a monthly statement in a PDF, so that's not easy to work with. If you have a history, or make a lot of transactions, manually entering them in to a spreadsheet may not be the most efficient....

For transactions, I enter them manually (so that I can enter them in the way I want to handle costs/fees calculations), which might not seem the most efficient. As for alternatives, my brokers can only provide transactions in password protected PDFs with 1 day lag, and it's a lot more work to save them from email to a fixed location and parse the different formats of PDFs than to simply enter the transactions manually myself. My trading volume is not super high so it's not a massive pain point for me at the moment.

Although I think Interactive Brokers does provide an API to users for trading, presumably that should also include existing positions. I have not explored that yet.

[wilsonleong]

This is an awesome tool you built! I'm wondering why how the first step involving importing the transactions work.

The function def InsertHistTransactions(start_date=datetime.datetime(2020,12,1)): seems like it's reading from an excel sheet and putting that data in MDB, right? What does the format of that excel sheet look like?

Also, about calculating portfolio returns, I saw you use the IRR method instead of Modified Dietz Return. How do you account for withdraws / deposits?

Hey Steve, Happy CNY! Yes you're right, the golden source of transactions are stored on an Excel spreadsheet. They are then inserted (or cached) into MongoDB so that the processes (calculations, plotting of charts, etc.) can query directly from MongoDB instead of reading all rows from the spreadsheet for every process. See https://github.com/wilsonleong/PortfolioAnalytics/wiki/How-to-plug-in-your-own-data for more details.

For calculating the portfolio returns (https://github.com/wilsonleong/PortfolioAnalytics/wiki/Calculation-of-Returns), I use IRR, which adjusts for cash flows, i.e. withdrawals, deposits, coupons/dividends, etc.