Import Script (Python Console)

This script automates two tedious accounting tasks: distributing packing and freight charges to items that share those costs and converting the result from foreign currency to your domestic currency based on the rate applicable on the day the shipment arrived, all because stuff needs to be properly valued in goods received.

This is no problem when you have, say a dozen of the same thing at the same price. But quickly it escalates when you are dealing with some main device and a dozen accessories all differently priced, and of course when your source randomly combines a diverse array of different shipments to save cost. When you calculate that manually, you have to go back and forth and repeat the same entries so often that the likelihood of making some little mistake is actually high. And then you have to do it all over again until you get it right and that's a bad thing, because you will get tired and prone to make even more mistakes.

In my scenario I already had a routine of entering invoices (always in foreign currency) one line per item in a lengthy excel file, each line including product name (or freight or packing charge), quantity, unit price and an arrival date for the whole transaction (for goods received and the rate). Huilding on that, the script takes as input the row number of the items that freight/packing will be distributed to and the row number of freight and packing. It distributes those costs, looks up the exchange rate and converts the item values to domestic currency unit prices. The results will be printed to the console and entered somewhere in the excel file. The code is very rigid but so are the actual restraints of that particular office task. Take care to check the results while looking at the original invoices, to check totals etc. There may be entry mistakes in the excel, for example. As weird as the console interface may be, it saved me a lot of time.

When calculating treshold values for customs (actually never had to do that myself) you may have a very similar problem (your item of interest may be above the treshold only after you properly assigned a share of freight charges to it, so you have to check).

I had no programming experience and just scrolled through a selection of chapters in Automate the boring stuff. But really, the basics (variables and fundamental syntax), some flow control and a rough idea of what data collections there are to be manipulated is already more than enough to get going at a fun rate of progress. The research of the specific issues basically sustains itself, you just solve one thing then move on to the next until it's done. Error discovery and fixing is fast because you can print out your messed up data collection and try correct stuff right after each failed run in the same window, without having to wait for a rebuild or anything.

Here is the code.

Copyright © 2016-2024