• From: Nicaragua, Leon
  • Registered: 05/12/2012
  • Posts: 74

Topic: Tax report as comma file or spreadsheet

Here in Nicaragua we have to upload monthly a VAT report in spreadsheet. Up to now we have done that manually, but as all the information is in the system, there must be a better way. I can get a nice VAT report out of Frontaccounting in PDF (even if the tax ID number is missing). Is there a way to make it printing to a comma file or spreadsheet?

I tried to look at the possibilities to make a MYSQL query directly in the database but I had some problems understanding the structure of the database (particularly understanding the ID that link the different tables), but must admit I am not very experienced in MYSQL databases.

Any help would be highly appreciated. smile

thorbjornw's Website

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: Tax report as comma file or spreadsheet

Can't you just print the existing tax report to Excel instead of PDF?

/Joe

  • tm
  • Senior Member
  • Offline
  • From: Sweden
  • Registered: 05/13/2013
  • Posts: 142

Re: Tax report as comma file or spreadsheet

I did that for Swedish VAT reports, by just querying the database for the balance (at the end of the period) of a bunch of accounts, adding a few of them together and outputting the appropriate figures inside the appropriate XML tags. (The script also creates a balancing transaction to be imported by the CSV import plugin which I use to import daily sales figures. I'm not familiar enough with the database model to suggest how to do this directly in SQL, but if you have the figures you could always do it manually.)

It's a (very) simple php script sending a ridiculous number of queries to the database. But it only runs once a month...

Something like 

select sum(amount) from gl_trans 
where account = $a 
and tran_date => $periodstart 
and tran_date =< $periodend

should get you the data for each account, assuming the VAT accounts were balanced at the start of the period. (I ensure that by booking the balancing transaction on the last day of the period. Otherwise you'd need to sum up all transactions of the account from the start of the year.)

Then you can just output the data into whatever format you need.

  • From: Nicaragua, Leon
  • Registered: 05/12/2012
  • Posts: 74

Re: Tax report as comma file or spreadsheet

Thanks for the suggestions. I think the easiest would be to print the tax report to an excel file. But how do I do that? The report comes automatically out in PDF format, and there is no easy way to convert that to a spreadsheet (I am working in Linux Mint 15).

thorbjornw's Website

  • tm
  • Senior Member
  • Offline
  • From: Sweden
  • Registered: 05/13/2013
  • Posts: 142

Re: Tax report as comma file or spreadsheet

I'm afraid I can't help you there. The files output here are perfectly good Excel files. Well... Good enough for LibreOffice, anyway. Can you get anything out of FA in Excel format? Could there be some library missing?

  • From: Nicaragua, Leon
  • Registered: 05/12/2012
  • Posts: 74

Re: Tax report as comma file or spreadsheet

No idea. When I make a report it always comes up on the screen in pdf format, and then I just print it out (invoices, GL reports etc.).  I have been looking if there is some option to print to spreadsheet instead but I am unable to see that.

thorbjornw's Website

  • From: Nicaragua, Leon
  • Registered: 05/12/2012
  • Posts: 74

Re: Tax report as comma file or spreadsheet

I have just tried an online PDF converting service, and that gets me a nice spreadsheet with the tax data. But it seems a bit odd way to do it, as I know Mysql can export directly to spreadsheet.

thorbjornw's Website

  • From: Nicaragua, Leon
  • Registered: 05/12/2012
  • Posts: 74

Re: Tax report as comma file or spreadsheet

Sorry. I had overseen the option at the bottom of the page to choose between pdf and excel. Now I got what I needed. SOLVED.

thorbjornw's Website