Illustrated tutorials

How to categorise transactions in Excel using DropToExcel

How to identify duplicate transactions in Excel using DropToExcel

Installing DropToExcel

DropToExcel is a very light-weight application.  The installation file is around 5Mb and takes a few seconds to run.  Other than Excel and Windows, DropToExcel has no special requirements, so you can get up and running quickly.

Once installed, you will automatically be given 3 months to trial the application.

You can run an example analysis from the Help menu, to check that the application is running and communicating with Excel correctly.

Then you can select amongst the options provided to set-up and save your ideal analysis template.

Finally, to generate your own transaction summary you can either select an Excel file using the File menu, or you can drop a file onto the DropToExcel icon on your desktop.

Who needs DropToExcel?

Anyone who receives bank account transactions, general ledger reports or supplier account summaries in a list and needs to analyse or compare the data in Excel with a variety of different settings, has a need for DropToExcel.

What is the cost of DropToExcel?

DropToExcel is completely free to download and use, with carefully selected advertisements, for 3 months. For an annual fee you can use an unlimited version of DropToExcel.

One of the many features of DropToExcel is the ability to allow users to output the summary options selected to Excel – this functionality helps to ensure transparency and accountability throughout the analysis process.

How much time will DropToExcel save me?

DropToExcel is designed to reduce user interaction throughout the transaction summary process to a minimal level. Therefore, any time that you or your team currently spend identifying and analysing categories, keywords, recurring transactions, duplicate transactions or unique transactions, can be saved by using DropToExcel.

I already create transaction summaries in Excel, why do I need DropToExcel?

DropToExcel makes it possible to create a consistent summary of transactions, showing aspects that are slow, inaccurate or impossible to generate manually. For example, DropToExcel will categorise transactions using specified keywords, in a few seconds.  Also, DropToExcel will display a list of the keywords used in your transactions, as well as the earliest date, latest date and total number/value.

What does DropToExcel require?

DropToExcel is a light-weight application without any special hardware requirements above those of Windows 7 (onwards) or Microsoft Excel 2003 (onwards). You will need Microsoft Excel 2003 (onwards) to use DropToExcel.

What is the settings summary?

DropToExcel gives users the option to output the settings that were used to generate the summary into Excel. This means that each Excel summary will be delivered with a detailed report showing (in a separate tab) the options that the user selected. For example, this will show the acceptable amount and day variance for identifying recurring transactions – seeing this information will help to ensure transparency and accountability throughout the analysis process.

What is a sponsored licence of DropToExcel?

This is a completely free licence for 3 months, which is supported by a selected sponsor that will be advertised on the interface of DropToExcel. This means that you will enjoy the full experience of creating one-click transaction summaries; functionality that includes identifying, analysing, comparing and categorising important transactions, amounts, periods and keywords.

Reversed transactions

Transactions in Excel that are reversed within a user defined number of days, with one or two credits that equal a debit, or one or two debits that equal a credit are identified in a single click. This quickly catches any disguised transactions that may be filtering through a bank account and potentially should not be classed as trading income.

Find weekend transactions in Excel

Identify unlikely or impossible transactions by day of the week (e.g. Friday) or month (e.g. 31 September). Suspicious transactions may occur in Excel for a variety of reasons, but they will be hard to spot across tens or hundreds of lines. DropToExcel provides an instant one-click solution for you to focus your time and energy on investigating the areas that are most likely to yield interesting results.

Unique transactions in Excel

Comparing transactions held in multiple files is no longer a lengthy and inaccurate process. Simply enter the acceptable date variance into DropToExcel and select the columns (date, description, amount and/or category) that you would like to use to compare the transactions, and the task will be completed for you.

For example, perhaps one file contains lines posted to a ledger, and another file lists is an export of transactions from the bank account and you need to compare the two for differences. Unique transactions can quickly focus your time on what is outstanding, or needs further investigation.

Duplicate transactions in Excel

DropToExcel identifies and separates duplicate payments or receipts using a combination of the date, description, amount and category columns, as selected by the user. An acceptable date variance can also be defined to allow for posting differences.

This is a one-click solution to the laborious and inaccurate manual task of comparing multiple rows in Excel, either within the same file, or across multiple Excel documents.

Categorise and separate transactions in Excel

Transactions are identified using a built in keyword list and prioritisation; these can be edited and added to by user. A separate tab can be created to show the identified transactions for as many of the categories as the user requires. An overview of the transactions meeting the keyword criteria of each category is provided, and is dynamically driven by individual tabs (where available).  This allows the user to delete and add rows, where required, and the summary tab is automatically updated.

Recurring payments or receipts

DroptoExcel identifies where the same amount is paid (or received) within a user defined period; quickly listing weekly, monthly or annual transactions across the data. The user can also enter an acceptable variance of recurring transactions to allow for differences in consecutive amounts. This means that (for example) direct debit or salary payments that change slightly each month can still be identified.

Using dates and amounts, each within an acceptable variance, helps to show regular financial dependencies that are imposed on the account holder. When analysing several months, patterns can be identified and possible future trends extrapolated using robust rules with documented evidence.

Detailed financial summary in Excel

DropToExcel performs a comprehensive summary of any number of transactions in Excel, in a single click. The user can set a variety of options so that the detail of the output is tailored for their needs. These options include setting the value of the minimum transaction for round amounts, the percentage of total credits that are material, and the number of Excel rows that should be reported in the summary tables (where applicable). In addition, the acceptable variance in days and percentage of consecutive recurring amounts may be defined.

Transaction keyword summary

DropToExcel identifies keywords by number of occurrences and total value, with a detailed breakdown showing the first and last date on which the word appeared in the transactional data being analysed.

The simple to use interface allows the user to set options so that multiple words can be aggregated together to meet the user’s needs. Keywords can also be located as separate words, or as part of other words, in a single click. Certain words may be specified by the user to be included or excluded from this summary table.

Keyword analysis identifies key stakeholders with whom a significant financial relationship exists. This summary is displayed in Excel as a table showing each identified keyword, the number of occurrences, the total transaction receipts and payments, and the percentage of total receipts or payments that this value represents.

Monthly balance analysis

End of day balances are automatically identified and summarised in Excel, showing the minimum, maximum and average for each month, and the overall period.

Analysing the available balance over a long period may indicate how seasonal the cash flow requirements of the account holder are.

Fortnightly cash flow analysis

The total receipts and payments are split between the first and second half of each month and shown as a value and as a percentage of the total monthly figures. Overall figures for all months are also displayed.

Showing regular significant receipts in one half of each month assists the user in assessing the quality of monthly cash flow management, together with identifying the likely payment structure that new financial commitments should take.

Analysing multiple bank accounts

Multiple files containing transactions for one or more separate bank accounts can be analysed in a single click using DropToExcel. Key sections of the summary output can be separated to ensure that the analysis of each account is a comprehensive representation of the data.

The sections that can be separated for maximum user insight are:
– Duplicate or unique transactions
– Intra account holder transactions
– Recurring or reversed transactions

The following sections are combined together to show an overall picture:
– Monthly summary showing credits, debits and account balance
– Average available balance after applying user specified exchange rate (if applicable)

Anti-virus warning

Our application has been checked for viruses and is provided with a digital signature that most anti-virus applications use to successfully validate the publisher of DropToExcel.