Python Programming On Win32

Putting It All Together: Importing Financial Data

Now we’ll write an import script using our wrapper. Excel is a good medium for entering financial data; users can enter data more quickly in Excel than in a database; Excel builds pick lists automatically; and it’s easy to copy, paste, and rearrange the data quickly. However, data entered in this way isn’t always properly validated. Imagine that someone in your organization is preparing a list of new invoices raised once a month and emailing the data in a standardized spreadsheet. You want to import it, validate it, get the data into double-entry format, and save it in a BookSet, as part of assembling monthly management accounts. The examples for this chapter include a spreadsheet called invoices.xls, which looks like Figure 9.3.

Figure 9.3. Invoices to be imported

You want to open this up, grab the three facts near the top, and import the matrix of cells describing the invoices. You’re not sure how many invoices to expect, so you need to count down. If it’s a cash sale (i.e., Date Raised is the same as Date Paid), create one transaction; otherwise create two: one for the bill and one for the payment. Note that some invoices are not yet paid; in this case, estimate a payment date for your forecasts and tag it as a scheduled transaction so that it can be filtered out of real accounts.

The full script to do this is in the file exceldemos.py, ...