Account Statements & Data Exporting - What formats would you want to see?

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)

8 Likes

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.

9 Likes

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.

23 Likes

Thatā€™s a complicated looking one, certainly something weā€™d like to add once we get the general mechanism up and running though!

1 Like

Have voted on those above.

CSV file format is the crucial element from my point of view.

1 Like

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

6 Likes

Can we have a REST API on a strictly read only basis with JSON responses?

6 Likes

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.

4 Likes

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.

2 Likes

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)

2 Likes

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.

3 Likes

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)

7 Likes

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 :grinning::grinning::grinning: you got this :wink::upside_down_face:

2 Likes

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?

2 Likes

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?

2 Likes

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 :upside_down_face::upside_down_face::slightly_smiling_face:

2 Likes

This is exactly what Iā€™m doing, actually - so good suggestion :slight_smile:

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.

7 Likes