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

[simonpoole]

Hi everyone!

One of the loudest and clearest requests from the community and our user feedback surveys is that you guys want to be able to export your data from the app - whether this be for tax purposes, tracking your portfolio in external services, or any other use.

As part of scoping, I’d like to gather a clearer picture on what data formats would be most popular - so we’d love to see nominations on potential formats from those interested.

To keep things simple and clear, feel free to use this format below:

Format: (e.g. statement, list, graph)
Data: (e.g. valuation, holdings, transactions, orders)
File: (e.g. PDF, CSV, etc)
Example format: Optional, but we’ll need a reference format to build these

Strictly no promises on dates since we’re busy with the Christmas List, but this is a feature myself and lots of other Freetraders are keen to create and use.

[Gaz92]

CSV with transaction data would be ideal! Something similar to info on the contract notes:

• Stock/ETF
• Buy / Sell
• Date
• Number of shares
• Price per share
• Stamp duty
• Currency
• FX Rate (perhaps broken down into base rate + fee)

And for dividends:

• Stock/ETF
• Ex Div Date
• Payment Date
• Payment Amount
• Currency
• FX Rate (perhaps broken down into base rate + fee)

[J4ipod94]

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)

[EuanG]

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.

[FailedTuringTest]

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.

[simonpoole]

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

[Mwjdavies (Martin)]

Have voted on those above.

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

[Eden (E)]

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

[TheLHInvestor (The Long Haul Investor)]

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

[jstar88 (Jeffrey)]

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.

[simonpoole]

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.

[Wulfy]

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)

[TheLHInvestor (The Long Haul Investor)]

I can imagine, I would struggle as a developer to authenticate this safely but one can dream.

[Wulfy]

To add to mine.

Basically whatever format that allows me to copy the transactions into simply wall street to track my portfolio.

[EquityInvestor]

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)

[Sentinelgre (The Spaniel in my photo is Archie)]

@simonpoole

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

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

[Eden (E)]

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?

[fire2045]

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?

[simonpoole]

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?

[esteban (Esteban Ferré)]

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…