How do I GET (download) data tables into Excel from the accounting software?

Learn how to download (GET) data tables and how filters, conditions and preset filters can help you mine your data before downloading in an Excel worksheet.

You can download (GET) data tables from your connected organisations into your Excel worksheet. There are few steps required to download the data as this can be filtered based on your needs.

Choosing the data table

  1. Open Ms Excel, click on the DataDear tab and login using your email and password.

  2. Choose the organisation (if you have more than one connected organisation) from the first drop-down.

:information_source: You may first need to add an organisation - refer to how you can:

  1. Click on the Data Table drop-down list to select the data table (data-set) you wish to download.

Data Tables

There are different raw data tables (depending on the connected company) which have been grouped by type to make it easier to identify - the list depends on the organisation and on the accounting software from where the data is to be downloaded.

  1. Once you choose a data table - example the Invoices, a parameters window will allow you to mine and filter the data before this is downloaded. You can choose to filter with a variety of fields.

  2. Preset filters - DataDear comes preloaded with a number of preset filters which can help you understand how the filtering works. You can choose a preset by using the Preset filters drop-down. You may also save your frequently used filters as a preset by typing a name in the preset drop-down and select the Save Preset. Custom presets can also be deleted.

  3. Date Range - You need to select the date range from a preloaded list (such as last month or this year) or choose a custom date range - only the transactions which fall within this range will be downloaded.

  4. Unique Records only - Remove any possible duplicated content such as a contact may have more than one telephone number (this may not be applicable for journals).

  5. Remove empty columns - Remove columns which do not have any data.

  6. Get additional details - For some data tables, there is also the option to Get additional details which download all the lines related to the transaction. This functionality is an improvement over the drill-down functionality.

  7. Filter your data - You may also decide if the filters will need to match all conditions or at least one of these conditions. You may apply a variety of filters based on any parameter (column) available in the selected data table. Select the Parameter (example Invoice Number), the Text Filter (such as Equals … or Contains…) and a Value. You may pick up the value from another cell or sheet which is ideal when you need to refresh data based on another sheet. You can also use Excel’s function wizard in these fields. Choose the + icon to add the filter for processing. Multiple filters can be added and applied to your query. You may also edit or delete these filters until you find the right balance for your needs.

  8. Tab Colour & New/Current Sheet - Choose the Tab Colour and choose if you wish to have the data downloaded in the Current Sheet or in a New Sheet.

  9. Raw Data - Choose if you wish to have the data downloaded as raw data (recommended when formulas will be applied on the downloaded data)

  10. Data will now be downloaded directly from the accounting software (such as Xero or Quickbooks) in an Excel table. You may delete any unnecessary columns, apply additional Excel based filters and save your data.

  11. Next time you open this worksheet you can refresh this data and all applied filters will be remembered.

Sample Data Table (Invoices)