Extracting Data to Excel

I am a new user of Manager.IO. Please may you let me know how to extract Data to Excel. For instance, if I want to extract Invoices for a selected period, to Excel, how do I do this ? Thank you for your help.

There are various means to export data to Excel

Which one is the best for you, will depend on what you are trying to achieve - it is possible that what you are trying to do is already possible in Manager

All reports have a Copy to Clipboard button
In the Sales Invoices tab, you could use the Batch Update feature or the Copy to Clipboard feature
You could create a Custom Report and then copy the results to the clipboard

Dear Joel

I can Copy to Clipboard, but I am failing to Copy to Excel – what am I doing wrong ?

Thanks

Jen

You use Copytop Clipboard in Manager

In Excel, you then use Paste to import the data
image

Hi Joel

Managed that okay now. But, if I want to select a certain selection of Invoices, say for the month of March 2022, how do I do that ?

You are most helpful !! Thank you……

Where did you copy the invoices from?

If it was from the Sales Invoices tag, you will have to try and select the invoices in the list - for example, enter /03/2022 into the search box

It is probably easier to select them in Excel

@JEN, you need to explain what you mean by “extract Data to Excel.” You can use the Copy to Clipboard function to copy the Sales Invoices tab listing and paste into a spreadsheet. But that copies only the information displayed, not the entire transaction record. You can go part way through the Batch Update process, stopping after copying the data and then pasting into a spreadsheet. But that will give you a lot of incomprehensible hexadecimal UUIDs. You can copy reports, but those are processed extractions of information from the database.

The real question is, what information do you want that is not already present with available features of the program?

Once a month, we have to do a VAT REPORT for the whole of the previous month (OUTPUT and INPUT). The OUTPUT VAT Comes from the Sales Invoices, the INPUT VAT comes from the Purchases Invoices. We have dual currency – ZWL (Zimbabwe Dollar) and USD, and we need to separate the Reports accordingly. My boss usually does it, but he is on leave at the moment.

I would have thought that the Tax Reports included in Manager would be sufficent for this - but maybe they do not split out the ZWL and USD separately

@GrahamvdR has often posted about ZWL/USD multi-currency issues. Perhaps he can offer suggestions.

Thanks @Tut for bringing my attention to this. @JEN, I will share what I do, but since I believe Manager is not fully equipped to handle our reporting requirements, it is quite likely that your boss has set your business up differently and my method either won’t work for you or will require a great deal of reconfiguration to get it to work.

The fundamental issue we have is the requirement to report all transactions in the currency of the transaction. Manager is set up to report everything in the base currency, and from my time on the forum it seems that our situation is unique, which is why it remains unsupported. See this topic for some more discussion around this, with acknowledgement by the developer that it is unsupported.

I outlined my method in this post, but I’ll run through it again here:

Firstly, I have set up my customers and suppliers so that all of those with their currency set to USD have “ (USD)” at the end of their names. Those set to ZWL (our base currency) have nothing added to their names, but of course you could use “ (ZWL)” or something similar to help you. I start by creating a Tax Transactions report for the period in question. This lists all transactions, grouped by tax code.

From here I click on Copy to clipboard in the top menu bar, and I paste the data into a spreadsheet. There I go through and separate inputs from outputs and ZWL from USD. I haven’t automated this yet, and so it takes some time. Depending on how many transactions you’re dealing with it may be easy, or completely impractical.

Once I have the USD transactions grouped together I make sure they are sorted by date, and I retrieve the exchange rates for the date range and add those into an adjacent column to perform the calculations back to ZWL for the report. I do the rest of the calculations and report preparation in the spreadsheet application, and only refer back to Manager to check the totals to make sure my calculations are correct.

I hope this makes sense and is of some help. Whether this approach will work for you might depend on how your customers and suppliers are set up (and how easy it is to separate them by currency in the tax transactions report), the volume and nature of transactions that you’re dealing with, and your skills in Excel. Somebody more skilled than me could automate it to a much larger degree, and others might struggle to do what I did. If you have any suggestions or ideas for alternative methods please do share. I would be curious to hear how your boss does it when he gets back from leave.

Note that if you do find yourself wanting to update thousands of customers to append “ (USD)” or “ (ZWL)” to their names, Manager has batch update functions that could help with this.

1 Like

Hi. I’m Jens boss and I’m back at work. This is the method I use, might be useful to others.

I have 2 VAT taxes. 1 is USD and the other is ZWL. So the taxes are separated from the start. to get the date range I will simply type Mar-22 and all the March 22 transactions will be selected, copy these to clipboard and paste into excel order by date and it’s done. Only headache is that the USD figures are not recognised by excel as numbers. Have to remove first 4 characters from the left. This is done with a formula, can never remember it but google provides every time. ZWL value of the USD transactions comes through with the copy paste.

A lingering headache is that the USD VAT payments never balance with the liability due to the moving exchange rate, so there is always a gain

1 Like

I am new here and thanks for you guys explain how to export the data into excel by simply using the copy to clip board function.

  1. You click on the Copy to clipboard button
  2. Open a new blank worksheet in Excel
  3. Paste the contents of the clipboard using the menu command Paste or using the keys Ctrl+V