I scan invoices and manage them by DT3. Whilest importing every file which is an invoice is being tagged »invoice«. Each file will be saved in the folder of the company wich has issued the respective invoice.
Now I want to have DT3 calculate the sum of the invoiced amount within the current calendar year.
How can I make it?
I think you’d be better off doing this query in your accounting/payment system. Extracting data like that from a PDF, while theoretically possible if you could figure out the pattern, probably is not practical for mere mortals to do. Just my two-bits, as they say.
First off, you’d need the amount for each invoice. You could use a script to find that and enter the value in a custom metadata field. Then you could run another script to calculate the sum.
However, as @rmschne pointed out, already finding the amount for every invoice is not a trivial task. Firstly, you’d have to find all the amounts in the invoice and then somehow™ figure out the final one. Going for the biggest value might be worth a shot, but if you get a discount regularly, this is not what you want.
Depending on your jurisdiction, invoice amounts also might not be what you want – in Germany, for example, many people are taxed by the “pay-by” date, not the invoice date. So invoices arriving in december might be taxed only in january.
Frankly, I’d not try to do what you’re doing. Use an accounting system, if you need to have correct numbers. As an aside: What is the point in having the invoices filed by company? Wouldn’t tags be more useful, if they allow to group by kind of expense?
I’ll give it a try by custom defined data field »price«. So, I could enter manually the amount to pay for each invoice. But now I do need a tool which summarizes all values in the price fields in the respective folder.
The reason for having the invoices filed by company is, my whole familiy is keeping synchronized all the documents via Nextcloud. But only I use DT3 and make all the accountings and the tax issues for the family. I want to keep in eyes, how much expenses occure e.g. for medical treatments or for car repair. For that purpose I merge the respective companies in intelligent groups. At the end I want to know the sum of the costs, which has being occured within the current year for an given purpose - or in case of medical treatments whren the point is reached when it would be reasonable to reimburse the expenses by the insurer.
For reporting, I use a script to extract the data from Devonthink to a spreadsheet via a .csv file
As a spreadsheet it’s easy to do calcs, charts etc
I run this monthly to produce budget/expense reports
My data is received receipts for any expenses
This can be emails, scanned paper, …
No folder structure; the DT records are tagged appropriately
The amount is manually entered, appended to the title
There’s actually a script command that attempts to extract amount from the contents
That’s what I meant when I said “tags”: I do not really care who sends me the invoice, but what it is for. Of course, an MD will probably not send you an invoice for a car repair …
I do a similar “service” in collaboration with a couple of my family members … I use Banktivity to draw transactions for the target bank accounts and report back to them. Very few of our transactions supported with invoices, but we scan and store what documents we have. Other banking apps available. Could even rely only on bank transaction exports to Excel.
That being said, if you can use your approach with DEVONthink and scanning/interpreting invoice documents, and setup up something to correctly and reliably extract data for analysis, go for it I guess.
The bank transaction file is a great resource
I download it from my bank in .csv format
and use a script to process the file, creating individual records