Finance Reporting

We have always had an issue with our library materials budget reporting due to the way invoices with foreign currency are paid. While we know approximately how much a foreign currency invoice will be in AUD (we look at the exchange rate at the time we receive the invoice), it isn't until our finance department pay it that the final AUD amount is known. We need the final AUD amount for reporting/analytics however since our Library management system (LMS) is not integrated with our institutional finance system, we have previously had a staff member produce a report out of the finance system and then transcribe the 'actual' AUD values into our LMS. Not the greatest use of staff time and prone to human mistakes in calculation - we only get a invoice total amount, not an amount per line item so there is a bit of maths to break it down.

I have a bit of experience in using our LMS API from a few previous projects so I set out to create a python script to automate the process as much as possible. Unfortunately we can't automate the generation of the report out of our finance system but the format of the report is in CSV so I was able to add a module to the python script that grabs the contents of the report for processing. A quick summary of the steps within the script:

1. Generate list of invoice numbers with invoice totals (from finance report)
2. Look up invoice number in LMS
3. Check invoice currency (ignoring anything in AUD)
4. Check invoice date (ignoring anything older than previous year)
5. Using existing values in LMS, determines the ratio of the invoice line to the invoice total
6. Using ratio in previous step - determines the invoice line total in AUD
7. Grabs the invoice line object out of the LMS
8. Updates the price note field with the calculated invoice line total (in AUD)
9. Uploads the updated invoice line object back into the LMS
10. Generates an export file with all the invoice line totals for manual checking if needed (an audit file)

As always, quite a few issues came up as I worked through the process:

• Invoice number is the only common identifier between finance and library systems but these may not be unique. Including a date check to ensure we are only reviewing recent invoices fixes this
• Initially retrieving the invoice object provides a list of children objects (invoice line objects). I was hoping to use these child records to update the LMS however converting the objects to XML, updating the fields, converting back to objects and uploading to the LMS always produced an error. It was easier to grab the object ID's and retrieve the invoice line objects later. Its an additional API call but it was much more reliable
• Some vendors are listed as attracting GST (as most transactions are in AUD) however this is not the case for foreign currency transactions. This causes the LMS to add a GST line when it shouldn't. This affects the Invoice total to line calculations. The script identifies any gst line and adjusts the invoice total to facilitate accurate line total calculations
• Initially we wanted the script to ignore any record that already had the price note field populated (so we didn't overwrite an existing value which was also an efficiency gain as the script skipped over any previously processed records) but we eventually decided that it would be better to overwrite any existing value that may be incorrect and then change the script back to ignore updated records after we did a bulk update

With this script up and running we have managed to save staff time and have also improved the accuracy of our Library Materials budget reporting. Not a huge savings/impact but considering the budget constraints being placed on us at the moment (which should be a seperate post) I'm pretty happy with it. Plus it provided me with an opportunity to work more in Python so there is an educational aspect - I love being able to problem solve through code! It also sets us up for an easier integration once the institution transitions to a new central finance application.