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

For a few things I use Apple Script to automate tasks. For instance if I start a new project, I run a script that creates basic things like folders, standard to-do’s and notes on my hard drive and in my Things and Evernote applications. They save me time and let me avoid a lot of repetitive work. I wrote those scripts years ago, and meanwhile I have forgotten what little I knew about Apple Script.

Now I’m trying to build a new script. I had thought about this already, then Frank Meeuwsen’s similar steps (in Dutch) triggered me to start.

During my reading online I save articles and documents into Evernote, which I tag and store.
I’d like to automatically create a Suggested Reading posting weekly based on what I save in Evernote. I imagine adding a specific tag for this to the things I save, so I can also save things without them showing up in such a posting. The articles I save usually have a short sentence about why it’s relevant to me.

This means:

  • Running the script automatically weekly
  • Selecting Evernote notes from the last 7 days with the right tag
  • From each Evernote extract the short descriptive sentence I added, the associated weblink, as well as other tags I added when saving.
  • Then build a bullet list, with the descriptive sentences as text, and the link embedded either at the end, with its title as text, or maybe embedded in the description, based on some sort of indication.
  • Select three random tags that occur at least twice in the list of links
  • Add those three tags as part of the title of the blog post
  • Add all tags used to the tags for the blogpost
  • Set Linklog as the Category
  • Save as draft in my WordPress blog, with a scheduled post date of 16 hours.
  • Send me a message inviting me to review the draft and post. (If I don’t review, the posting will thus automatically appear)

I used to use a bookmarking service like Delicious or Diigo, and there used to be ways, or maybe still are, to blog automatically from their service. However it would necessitate me to save everything twice: As a bookmark and as a full article in Evernote. (Saving the entire article circumvents issues with link rot and paywalls, and allows me local full text search in all my notes)

I’ll likely suffer hours of frustration trying to find out how to do things correctly in AppleScript. Any pointers to useful resources (example libraries for instance) are therefore welcome.

(And yes, I understand the discrepancy between wanting to write a script to work with Evernote, while simultaneously wanting to leave Evernote)