Format: List/Statement
Data: transactions, orders
File: CSV
Example format: Headers being ā Ticker, Name, Exchange, Currency, Quantity, Cost per Share, Stamp Duty, FX Rate, Date Time, Type (Buy/Sell/Dividend)
Holdings file in CSV format. Selectable which wrapper itās for, so ISA or GIA on their own, or ISA & GIA together, or ISA & GIA & SIPP (when it arrives) but one under another rather than combining them.
Columns:
- Ticker
- Stock/ETF name
- No. of units held
- Base currency price (pence Iād think but maybe selectable between pence and)
- Local currency price (again selectable may be useful between cents and dollars if US assets)
- Current value in Ā£ with a total at the bottom
- Cost in Ā£ with a total at the bottom
- Gain/loss in Ā£ with a total at the bottom
- Gain/loss % with a total at the bottom
- Cost per share in pence or Ā£ may be useful but thatās easy to work out from the columns already above
A timestamp of when the file was created would be useful.
Oh and if there was a cash balance weād need it added to the current value column above for an overall total in Ā£. Or whichever currency the account is based on for the new European investors.
For myself, I am interested in this for tax reporting purposes in my taxable account. As an example, Equiniti provide a āConsolidated Tax Certificateā that looks like the pictures below. Iād really like a report that is clear, simple, and tells me which number to enter in which box on my tax return. Iād like to be able to download it as a PDF that looks official, that I can attach to my tax return if needed.
Thatās a complicated looking one, certainly something weād like to add once we get the general mechanism up and running though!
Have voted on those above.
CSV file format is the crucial element from my point of view.
Format: Downloadable list/statement based on selected date range
Data: All cash and investment transaction data
File: CSV (optional PDF would be good, but CSV first)
Example format: As an example see Vanguards report generator for āclient transaction dataā
A CSV of all data I think would provide the most value for return on development work as its the most versatile format. But it is potentially more complicated for some investors to use. I recon it would be of more benefit than not however. A PDF that could be generated would also be valuable for me, however, a CSV allows everyone to generate their own reports in any way they wish.
CSV / PDF example
Example from Vanguard of their PDF generates transaction data
CSV example from Vanguard of this includes a tab for each account type with the following tables
Cash Transactions
- Date
- Details
- Amount
- Balance
These fields encompass, deposits, withdrawals, dividends, fees, buy/sell from/to cash/investments. anything related to cash changes.
Investment Transactions
- Date
- Investment Name
- Transaction Details
- Quantity
- Price
- Cost
Additional fields that would be useful
- FX
These fields encompass all trade/investment related data for each buy/sell
Of the two table types, I think the investment transactions are the most important ones
Example from the spread sheet (partial investment transactions)
Example of the cash transaction table
UI Example
I included some screenshots of the Starling bank UI for statement generation which I think is probably one of the better ones Iāve seen imo. Something like this would be very nice to allow us to specify date ranges for CSV downloads or statement downloads.
Additionally the other thread also links to other related topics that im sure youāve already seen Documents & Reporting
Can we have a REST API on a strictly read only basis with JSON responses?
TYVM for recognising the importance of this must-have feature. If possible, please deliver for early January so we can use for tax assessments.
IMO you need two options: 1) CSV with all transaction detail for the more data-savvy; and 2) PDF with straightforward layout and summary data at the bottom. Make it easy to use and comprehend for your core users first, and then dive-in to the cool REST API geekery.
So an API wonāt be included in this piece of work, but lots of the components for generating the reports could likely be re-used.
The main issue with an API would be making sure it has effective authentication, the magic link format isnāt particularly compatible with RESTful APIs.
Great idea.
Format: List/Statement
Data: transactions, orders, holdings
File: CSV
Example format: Headers being ā Ticker, Name, Exchange, Currency, Quantity, Cost per Share, Stamp Duty, FX Rate, Date Time, Type (Buy/Sell/Dividend)
I can imagine, I would struggle as a developer to authenticate this safely but one can dream.
To add to mine.
Basically whatever format that allows me to copy the transactions into simply wall street to track my portfolio.
File: CSV and PDF ideally. CSV to track the portfolio and make our own calculations but pdf in case any questions get asked, particularly in certain countries (thinking about the expansion) were the tax authorities require actual statements from the broker/platform. Please include the FT logo, company name and contact details as sometimes tax authorities can be a bit picky.
Data:
For purchase&sale transactions we need the following:
-Date of each transaction
-Any tax paid (eg. stamp duty)
-Price of purchase in foreign currency
-Exchange rate of acquisition
-Commissions of acquisition (eg. FX commission)
-Total cost in local currency
-Price sold at in foreign currency
-Exchange rate of sale
-Commissions of sale
-Total cost in local currency
-Profit/loss for position in local currency
At the bottom:
-Total profit or loss in the time period of the statement
-Additional fixed expenses paid (Not the commission, but fixed payments such as payments for ISA, FT Plus, etc)
For dividends:
-Date of payment by company
-Date of payment by FT to investor
-Dividend per share paid in foreign currency
-Total dividend paid in foreign currency
-Withholding tax in foreign currency
-Exchange rate
-FT Commission in local currency
-Total dividend paid in local currency
-Total withholded tax in local currency
Note: Please make sure that all transactions for the same stock are listed separately as for example some tax authorities assume that when a sale is executed, the oldest shares are sold. For example, lets say you cost average into a company and buy 100 shares in 4 batches. If you then only sell 50 shares, in some countries tax authorities assume that the 50 shares sold are the āoldestā 50 shares acquired so the first shares that were purchased by the investor and will apply any capital gains taxes based on the share price of that acquisition. Hence the importance of listing every transaction separately to be able to determine the adequate purchase price (not necessarily the average)
No offence to you, but thereās absolutely nothing complicated about that whatsoeverā¦
Iām not saying itās a gold standard you guys should aspire
But I am saying
That words matter!
And since all your customers are behind you (we prove that with, you know depositing money an stuff) you should be more confident about what can and should be achieved you got this
Itās far more work than a CSVā¦ Id assume by complicated theyāre considering that theyād need to accurately split everything into their appropriate groups, sanity check it, then run all the appropriate tax calculations on it etc. + the bundles of testing to ensure the calculations account for every abnormality, then get it approved and signed off from legal/finance since its a tax certificate document.
Otherwise i do agree it would be good to get those documents eventually, id assume once a robust document generation backend is made these kind of things would be far simpler to make?
it would be cool if we can see details of how much total dividends we have received since our investments. Any ideas if this will be implemented?
I would think so! Are you thinking as part of a capital gains / transactions type report, or itās own report with just a list of dividends received?
An exporting function taking into account the diverse tax reporting requirements of each country youāre operating into would be great!
So UK for now but then Sweden, France, Ireland, Germanyā¦
@Eden I donāt necessarily think this is insurmountable but I think we need to be careful here because a) it should not be up to freetrade to do tax calculations and neither should we expect them to. & b) all the main data that will allow us to do tax returns and the likes is already in the app they just have to figure out a neat way to export them out to us.
Sure if they want to throw in fancy statements that aggregate stuff Iām all for that, but it really shouldnāt be that difficult to return data that they already control back to each user.
To throw my two pence in (hint hint @simonpoole) I think the simplest way to start all this is individual transaction download on a weekly/monthly/yearly level.
Ie you bought this much of that symbol at that price at that time and date within this timeframe.
Once they implement that as a feature they can start building on it
This is exactly what Iām doing, actually - so good suggestion
Starting with initial easy statements and the actual mechanism for requesting and delivering statements/reports - once that is done, the work for adding each individual format is a relatively small, self-contained chunk of work.