A standard SparkReceipt report (CSV, Excel or PDF) lists the documents that match your filters, with Expenses and Income in separate sections. Each section ends with its totals and a total for each Category. In Excel and CSV the rows run in date order, oldest first. Every document keeps its own currency, and when some documents are in another currency the report adds the amount converted to your account's Home currency. Set Row format to One row per line item to get each product or service on its own row, in its own Category.
This article explains what you get and which setting changes what. To create a report, see How to create reports in SparkReceipt.
The columns in an Excel or CSV report
An Excel or CSV report has one block for Expenses and one for Income. Each block starts with its title row (Expenses or Income), then the column headings, then one row per document. In Excel, if you chose One row per line item, a one-line explanation sits above the first block. So the column headings are on the second row, or the third in that Excel case, under the title. When you sort or filter in Excel or Google Sheets, select the heading row first.
The columns every report has include Date, Document #, Type (Expense or Income), Name, Categorization (the Category), Currency, Subtotal, Tax, Total, Tax Rate, Status, Review Status, Payment Method, Reference, Tags and Details. A few columns appear only when they apply:
- User, when the account has more than one active user (Accountants not counted)
- GST and PST, when documents in the report carry Canadian GST and PST
- One VAT column per rate, when your account has VAT rates set up under Tax rates
- Tip and Shipping, when any document has them
- Target Currency, Exchange Rate, Converted Subtotal, Converted Tax and Converted Total, when any document is in a currency other than your home currency (see Currency in reports)
- Item, Quantity and Unit Price, with One row per line item
- Web Link, when you choose an option under Include links to the documents
- Document Files, when you include the receipt files
- Your AI Fields, when you pick them under AI Fields in the report options
The Excel file has three tabs: Documents with the rows and totals, Category Summaries with the totals per Category, and Metadata with the account name and the report's date range.
Totals for each Category
Every report already totals your documents by Category, so a "total expenses per category for the year" report is a standard report with that year as its date range:
- Excel: the Category Summaries tab has one row per Category with Count, Subtotal, Tax and Total, for expenses and income separately.
- CSV: the same Category rows follow the totals at the end of the Expenses block and the Income block.
- PDF: the Expenses section and the Income section each list their documents under their Category (unless you chose a chronological order) and end with a table of Categories with their totals.
There is no option to put each Category on its own tab in Excel. To work with one Category at a time, filter the Categorization column in the Documents tab, or create a report with only that Category selected (see the next section).
The Overview also shows Expenses by Category and Income by Category for any date range, and its export gives the same figures as a PDF and an Excel file.
Choose which Categories are in a report, or leave some out
To include only some Categories:
- On the web app's Create report page, pick them in the Subcategories filter.
- In the mobile app's Create Report view, pick them under Item categories.
When you filter by Category, the totals count only the line items in the Categories you picked, not each document's full total. A credit card bill with one line in your chosen Category adds only that line.
To leave Categories out, start from Documents in the web app. Click Filter, choose Category, pick the Categories and switch is to is not. Then click Export. This leaves out every document that has a line in one of those Categories. See How to search and filter documents for the filters.
The same approach works for a report per supplier: filter Documents by Merchant / Payer and click Export.
One row per line item
The Row format option decides what a row is:
- One row per document: each document is one row with its totals. This is the default.
- One row per line item: each product or service on a document gets its own row, with Item, Quantity and Unit Price columns and the line's own Category in Categorization.
Row format is in the export dialog under More options, on the web app's Create report page, and in the mobile app's Create Report view. A document without line items still appears as one row.
There is no button to copy line items out of a document. A report with One row per line item in Excel or CSV is the way to get them into a spreadsheet. For example, a report of one Category for the year, one row per line item, lists every item you bought in it with quantities and unit prices. SparkReceipt does not keep stock levels or an inventory count, so treat that list as a purchase history you can build an inventory list from.
Why a split document shows under one Category
With One row per document, each document is counted under one Category: the one with the largest amount on that document. This applies to the PDF's listing and to the Category totals in every format, unless you filtered the report by Category. So if you split a bill's line items into four Categories, the whole bill still lands under the Category with the biggest share.
To count each line under its own Category, set Row format to One row per line item and create the report again. The rows and the Category totals then follow each line's Category. Reports you already created keep their old layout, so run a new one after changing a line's Category.
Date order: oldest first
- Excel and CSV rows are always in document date order, oldest first, within Expenses and within Income.
- PDF reports group documents by Category by default. Set Sort documents to Chronological (earliest first) to list them as one timeline, oldest first. Documents without a date come last.
The date in a report is the document's date, the date on the receipt or invoice. The order you choose in the Documents list (for example Date, oldest first under Sort) does not change the order in a report.
Currency in reports
Reports are in your account's Home currency, the currency set in Account settings under Account & Users. Each row keeps the document's own currency in the Currency column, and Subtotal, Tax and Total are in that currency.
When at least one document in the report is in a different currency, the report adds Target Currency (your home currency), Exchange Rate, Converted Subtotal, Converted Tax and Converted Total. A CSV with both the original amount and the converted amount is therefore a normal CSV report. The totals at the end of each block and the Category totals are always in the home currency.
The exchange rate is the rate for the document's date. If you set your own rate on a document (the Conversion rate row under its amounts, then the edit button), the report uses that rate.
A few things reports can't do:
- Pick another currency for one report. To get a report totalled in, say, USD, the account's Home currency has to be USD. Changing it changes the currency for the whole account. See What currencies does SparkReceipt support?
- Leave the totals unconverted. Each row keeps its original currency, but the totals are always in the home currency.
- Filter by currency. There is no currency filter. Export everything for the period and filter the Currency column in Excel or Google Sheets.
Exports of bank statement transactions follow the same rule: each transaction keeps its currency, and the converted columns are added when a transaction is in a currency other than your home currency.
Document names and file names
The Name column shows the document's name, usually the Merchant or Payer that SparkReceipt read from the receipt. If you rename a document, reports show the new name.
The name of the file you uploaded is not a column in reports. When you include the receipt files, they are named after the document's date, name, currency and total in the ZIP, and the Document Files column lists those names. If you need the original file name in a report, type it into the document's Reference field, which is exported as the Reference column.
Only documents with tax
Reports have no filter for "only documents with tax". Export the period you need to Excel and filter the Tax column, or use the per-rate VAT columns or the GST and PST columns where your report has them. For tax totals, see Can SparkReceipt create a GST, BAS or VAT report, or export to TurboTax?