Wrote a script that will cut back the time I need to do quarterly book keeping and VAT tax returns. It takes bank journal entries and creates the right procurement journal entries from it. An earlier script automates pulling transactions from my online banking environment and put them into my banking journal. These two steps are the most time consuming in doing the books each quarter. As in the coming week I will do the 4th quarter books of 2018, I will find out soon how much time exactly it will save me.

I’m trying to do weekly small hacks this quarter. This week I did three. One because I came across it in my feedreader, and two because they were of immediate practical use this or next week. So I’m slightly ahead of schedule with 5 hacks in 3 weeks.

I do the bookkeeping for my companies myself. An old habit, started when I did the necessary training to be able to poke around in a client’s financial system over 20 years ago.
For my companies I’ve built a spreadsheet that does all the necessary things for me.

My banks provide statements in MT940 format. They assume you have some way to import them into your bookkeeping system. As I run my own spreadsheet I don’t have import functionality for that. Doing the third quarter bookkeeping recently I realised it would be good to automate adding the bank transactions. Yesterday I wrote an Applescript that takes a MT940 file and transforms it into CSV, which I can easily add to my spreadsheet.

I export the MT940 file for the period after my last download from the online banking environment, and save it as .txt. The scripts takes it, and results in a .csv file of the same name.

MT940 presents the transactions per day like
mt940 source file

The lines :60F: and :62F: show the balance at the start and end. In between are the transactions.
Every :61: line contains an amount debited or credited. The first character after an amount is N.
These lines are followed by :86: lines that provide the description. This description can be multiple lines long, with the actual description shown between /NAME/ and /REMI/.

In Applescript I read through the lines, and create a CSV line for every transaction in the way I need it in my spreadsheet (in the form of date;month;description;amount debit;amount credit), and adding a final line of the balance at the start and end of all transactions (across the entire period in the source file). That last bit I can use as a control.

csv output from the mt940 source file above

The csv I can easily import in my spreadsheet and then copy over into the bank account journal.
Now I am curious to see if my other bank uses MT940 in the same way, or has implemented the standard differently. (I learned from my EDIFACT days that even if things are standardised there will be big differences between implementations due to interpretation and mapping internal processes and data structures on a standard.)

The applescript is available on GitHub.

My Applescript to start a new project both in my todo-app Things and my note-app Evernote throws errors for the Things part suddenly. After upgrading Things 2 weeks ago. Have the applescript hooks changed? Or the data structure of Things? Need to explore.

I use an applescript at the start of new activities. Depending on the nature of the activity (client project, project acquisition, internal, learning, personal etc), it automatically populates my folder system, my todo-app and my note taking app with the right information. Folders, standard tasks for a project, templates for documents needed, bookkeeping requirements, setting a consistent name and tag for the project across all tools etc.

I’ve finished building an AppleScript for automatically creating a Suggested Reading blogpost from my Evernote bookmarks quicker than I thought.

Mostly because in my previous posting on this I, in an example of blogging as thinking out loud, had already created a list of steps I wanted to take. That made it easier to build the step by step solution in AppleScript and find online examples where needed.

Other key ingredients were the AppleScript Language Guide, the Evernote dictionary for AppleScript (which contains the objects from Evernote available to AppleScript), the Evernote query language (for retrieving material from Evernote), and the Postie plugin documentation (which I use to mail to WordPress).

In the end I spent most time on getting the syntax right of talking to the WordPress plugin Postie. By testing it multiple times I ultimately got the sequence of elements right.

The resulting script is on duty from now on. I automatically call the script every Monday afternoon. The result is automatically mailed to my WordPress installation which saves it as a posting with a publication date set for Tuesday afternoon. This allows me time to review or edit the posting if I want, but if I don’t WordPress will go ahead and post it.

There is still some room for improvement. First, I currently use Apple Mail to send the posting to WordPress. My default mail tool is Thunderbird, so I had to configure Mail for this, which I had rather not. Second, the tags from Evernote that I use in the title of the posting aren’t capitalised yet, which I would prefer. Good enough for now though.

I’ve posted the code to my GitHub account, where it is available under an open license. For my own reference I also posted it in the wiki pages of this blog.

The bookmarks to use as listed in Evernote..


…and the resulting posting scheduled in WordPress

This is an AppleScript I wrote to automatically post weekly overviews of bookmarks I made in Evernote, as a Suggested Reading post on my WordPress blog.

set singletaglist to {}
set multitaglist to {}
set alltaglist to {}
set allbulletlist to {}

--get the notes from Evernote
tell application "Evernote"
set query_string to "notebook:\"linklog\" created:day-7" --get notes since last week
set notities to find notes query_string --gives you list of relevant notes
set notenum to count of notities
repeat with notitie in notities
--for every note fetch title, tags and source
set titel to title of notitie
set bron to source URL of notitie
set taglist to tags of notitie
set numtags to number of items in taglist
--the taglist needs to be processed
repeat with counter from 1 to numtags
set tagtest to name of item counter of taglist
--if tag already encountered it goes to multitaglist
if (number of items in alltaglist is 0) then
copy tagtest to end of alltaglist
else
if (alltaglist contains tagtest) then
copy tagtest to end of multitaglist
--if not encountered it goes to all tags
else
copy tagtest to end of alltaglist
end if
end if
end repeat
--fetch content and cut down to first line
set inhoud to ENML content of notitie --gets you en xml, we want text between and


set AppleScript's text item delimiters to "" --text to the right of
set tinhoud to text item 2 of sinhoud
set AppleScript's text item delimiters to ""
set inhoud to ""
set sinhoud to ""
set thisbullet to {titel, bron, tinhoud} --list of this particual ever note
set end of allbulletlist to thisbullet
end repeat
end tell
-- we got what we need from Evernote
-- we now have a list of all items, but need to look at tags to create the title of post
-- I want a title like Suggested reading: tag1, tag2, tag3 and more
-- where two tags are used more than once and one just once
-- alltaglist contains all tags used, multitaglist contains all tags occurring multiple times
-- now create a list of tags that are used once (basically alltags minus multitags)
set allnum to number of items in alltaglist
set doubles to number of items in multitaglist
repeat with counter from 1 to allnum
if (multitaglist does not contain item counter of alltaglist) then copy item counter of alltaglist to end of singletaglist
end repeat
set singles to number of items in singletaglist
--I need 3 tags for the title, two used more than once, and one single. Unless there's not enough.
set posttitle to "Suggested Reading: "
if (allnum < 4) then -- too few tags, use them all repeat with counter from 1 to allnum - 1 set posttitle to posttitle & item counter of alltaglist & ", " end repeat set posttitle to posttitle & item allnum of alltaglist & " and more" else -- at least four tags available set takedouble to 2 --default value if (doubles < 2) then --take from doubles what possible set takedouble to doubles --0 or 1 end if if (singles = 0) then -- take all from doubles set takedouble to 3 end if --in all other cases default value works -- with value of takedouble now build posttitle if (takedouble = 3) then -- take first & last, and in the middle from doubles set middlelist to round doubles / 2 rounding up set posttitle to posttitle & item 1 of multitaglist & ", " & item middlelist of multitaglist & ", " & item doubles of multitaglist & " and more" end if if (takedouble = 2) then -- take first & last from doubles, first from singles set posttitle to posttitle & item 1 of multitaglist & ", " & item 1 of singletaglist & ", " & item doubles of multitaglist & " and more" end if if (takedouble = 1) then -- take first from doubles, first and last from singles set posttitle to posttitle & item 1 of multitaglist & ", " & item 1 of singletaglist & ", " & item singles of singletaglist & " and more" end if if (takedouble = 0) then -- take first middle & last,from singles set middlelist to round singles / 2 rounding up set posttitle to posttitle & item 1 of singletaglist & ", " & item middlelist of singletaglist & ", " & item singles of singletaglist & " and more" end if end if -- we now can start building the blog posting -- title is available posttitle -- now let's build the html for the posting --first opening line and start of UL set blogpostext to "

Some links I thought worth reading the past few days

    "
    --for each bullet in bulletlist a new LI
    --with the blurb, and the title as href to the link
    repeat with listitem in allbulletlist
    set thislisitem to "

  • " & item 3 of listitem & ": " & item 1 of listitem & "
  • "
    set blogpostext to blogpostext & thislisitem
    end repeat
    --end the ul started at the top
    set blogpostext to blogpostext & "

" --we got it all now
--mail it to wordpress cat linklog, tags all tags, title and body.
--the content of the mail is determined by the Postie plugin settings
--the plugin used in my WordPress
-- cat and title in subject, rest in body: delay to post must come first, then body, then tags after one empty line
set mailsubject to "standard//[Linklog] " & posttitle
set tagtext to ""
repeat with counter from 1 to allnum - 1
set tagtext to tagtext & item counter of alltaglist & ", "
end repeat
set tagtext to tagtext & item allnum of alltaglist
set mailbody to "delay: 1d
"
set mailbody to mailbody & ":start " & blogpostext & "

tags: " & tagtext --don't use :end delimiter

--now send it as email
tell application "Mail"
set myMessage to make new outgoing message with properties {sender:"your@mail.com", subject:mailsubject, content:mailbody}
tell myMessage
make new to recipient with properties {address:"yoursecret@blogmail.com"}
end tell
send myMessage
delay 5 --wait for the mail to be processed
quit --quit Mail as I don't use it for anything else
end tell