An AppleScript Massage

George Wright
gwright@labyrinth.net.au

Back to the List of Tutorials

Share Portfolio Data for Quicken
Dear Kel, My understanding of the problem we discussed on the phone yesterday is that you download daily information about your shares from the CommSec web site and you would like an AppleScript to simplify importing the share data into your accounting program. That sounds like an interesting little task of massaging the data into another format and I would be happy to have a go at it. Could you email me a sample file of the downloaded data and another sample of the file ready for import into your Quicken 2004. Regards George
Hi George, Herewith the files as discussed. The file shown in Fig 1 is as downloaded from my watch list. The data is comma-separated with a carriage return at the end of each record and a blank line between records. Fig 2 shows the format I hope Quicken will import. I'll be interested in your comments. Regards, Kel

Fig 1. Comma-delimited text file of the CommSec Watch List
Fig 2. This file should import into Quicken?
Dear Kel, Attached is a file aTempQif.qif output by my first attempt AppleScript. Starting from the third line of the Comma-delimited file it extracts items 2 and 5 - the Stock and the Last price - and adds the current date, the extra commas and the zero. It also inserts a caret "^" between records. I don't have a copy of Quicken myself so I can't confirm that it will import. George
Dear George, I really can't fathom this. Your aTempQif.qif won't import. It looks OK but it just wasn't recognized by Quicken. Kel
Dear Kel. Make sure you don't modify and save the import file in TextEdit It might look the same but actually be in rtf format which adds lots of extra bits to confuse the import. Be sure to save as text or as ASCII with Macintosh line endings (ASCII 13). If I want to alter a copy of the original downloaded data file with some made up figures I use BBEdit Lite with Macintosh line endings.
Got that George. I Use Tex Edit Plus and save as ASCII. Kel
Start Again
Dear Kel Thanks for the CD copy of Quicken 2004. You are right - it won't import the file produced by my AppleScript. However I had a look into the User Guide.pdf document and found some information on importing. On page 313 they show five different formats for the data that will import into Quicken 2004. What we were using is the format of the exported data file of Quicken.

Fig 3. Quicken will import any of these formats
The first of these formats is simplest. It just has the code for the stock and the last price separated by a comma. So I will rejig the AppleScript to produce a text file with the required head and tail and with paragraphs containing just these two items. I'll leave the dates out on the assumption you download and import on the same day. I found some confusion between the date formats day/month/year or month/day/year in the application. So it is easier to leave date out.
The AppleScript
If you bear with me Kel, I'll try to explain how I constructed the AppleScript. The first task is to open the downloaded text file in TextEdit and count the number of paragraphs. The sample data looks just like Fig 1 and happens to have 37 paragraphs.

Fig 4. Choose file, open in TextEdit and count paragraphs
The data we want is in the odd numbered paragraphs (lines) starting from paragraph 3. In Fig 5 you see I have added a repeat block to turn each of these paragraphs into a separate list which is sent off to be processed by the script writeToQif. The line run writeToQif is commented out for the moment but it will be used in the final version. In the result window is the last thisStockFields list. We have to extract items 2 and 5 from lots of lists like that one.

Fig 5. The repeat block changes each record into a list of fields
The Output File
We need somewhere to write the bits of data extracted. The little script in Fig 6 will create a text file on your desktop and write the "!Type:Prices" & carriage return header as specified back in Fig 3. The myEOF variable keeps track of the end of file. We know how much we have written so the next write can start from there.

Fig 6. Create the output file on Desktop, write header then close
Remember that every new run of this bit of code writes new data from the beginning that clobbers earlier writes. I have a bit of extra code in the final version (Listing 1 below) that adds a few characters to the filename so that each file has a new name. See the variable timeCode in Fig 6 above. The script begins at script makeTimeCode. It extracts digits from the minutes and seconds of the string representing current time and these get tagged onto the name of the file. Another little script shown in Fig 7 will be required to write the tail caret "^" as the final item in the file. This doesn't get called until everything else we need has been written.

Fig 7. Writing the "^" tail to the file
At last we get to the bit that writes data to the file - that's the writeToQif script mentioned in Fig 5.

Fig 8. Construct a comma separated record and write to file
In Fig 8 you see the script opens the same output file and then makes a data record qifrecord out of items 2 and 5 of the original data - separated by a comma and terminated by a carriage return ASCII character 13. Note there is a try block to detect errors and make sure the file is closed when the script has run.
The Complete AppleScript
Dear Kel, Attached find the AppleScript QuickenMassage.scpt saved as an application. Give it a try with some real data and let me know how you get on. George
An excellent result, George... I have to say I'm very impressed.
Many thanks. Much appreciated. Regards, Kel
Dear Kel, No worries. I have a similar task needing attention. When I download CSV bank statements and import into Excel there is a lot of unwanted stuff that I usually get rid of by hand. Your little project has given me the spark I needed to get on with the job of automating it. By the way Kel, I did experiment with scripting Quicken so the import into your portfolio can be included in the script. However Quicken is not AppleScriptable which means you have to go to that application and import the new file generated by my script using the normal menu commands. But it is possible work around this limitation using GUI Scripting. In simple terms this amounts to getting application System Events to click the various menu items of the Quicken menu bar for you. It does work but I think I'll leave it out and keep it for some other time. Matt Neuburg has a good introduction to GUI Scripting in his AppleScript The Definitive Guide. Also you mentioned on the phone that you download weekly aggregate figures including Dow Jones, NASDAQ and AAPL and you want to massage these figures for import to Quicken. I suggest you can easily modify a copy of our script so that the file generated has a different name. Then modify the new script - at the set qifrecord line - so it extracts items 3 and 4 rather than 2 and 5. Save the modified script with a diffrent name. Listing 1 below is of the complete AppleScript. Copy and paste into Script Editor. After you test and maybe modify it you should save it as an application so you can run it from a double click. Better still install Script Menu - instructions in AppleScript Help - and drop QuickenMassage into the Scripts Folder so it will be easily accessible from the Script menu. Good luck with the shares. George.
This article and others I have published in Ausom News can be found on my website http://www.labyrinth.net.au~/gwright

Listing 1
(* An AppleScript 'QuickenMassage.scpt' *)
(* takes a CommSec watch list downloaded as *)
(* a comma-delimited text file and converts it to a .txt file suitable for importing into Quicken.*)
(* Author George Wright 2/04/2005 gwright@labyrinth.net.au *)
global tempQIFfile, thisStockFields, myEOF, fileDate, pickedFile
set myEOF to 0
set text item delimiters to ","
(* Generate an identification stamp "timeCode" based on the minutes and seconds of current time *)
(* Use stamp as part of the temporary QIF filename. Each time program runs a new file generated *)
(* Create the file in the Desktop folder. Write a qif header to that file.*)
(* Note however the "timeCode" is not unique. It is possible to get a file of the same name *)
(* If you run the program on exactly the same minute & seconds in the next or subsequent hour *)
(* Be sure to copy and rename files you want to keep *)
script generateTempQIF
property timeCode : "0"
script makeTimeCode
set d to current date
set d2 to time string of d
set k to (get characters -8 thru -7 of d2) as list
set m to (get characters -5 thru -4 of d2) as list
set km to k & m
set text item delimiters to ""
set timeCode to (get every item of km as text) as integer as string
set text item delimiters to ","
end script
(* Create a temporary file to store the Quicken formatted records *)
script makeTempFile
try
set myDesktop to path to desktop folder as string
set thePath to myDesktop & "aTempQif" & timeCode & ".txt" -- Save in your Desktop
set tempQIFfile to a reference to file thePath
open for access tempQIFfile with write permission
write "!Type:Prices" & (ASCII character 13) to tempQIFfile -- The QIF header
set myEOF to get eof of tempQIFfile -- The start point for the next write
close access tempQIFfile
on error
close access tempQIFfile -- just in case something goes wrong with write to file
end try
end script
run makeTimeCode
run makeTempFile
end script
run generateTempQIF

(* The "writeToQif" script selects fields 2 and 5 from the comma separated record of the stock.*)
(* It writes these two fields to the temporary text file *)
script writeToQif
try
open for access tempQIFfile with write permission
(* Here the data of a single stock is formatted so that Quicken can import it *)
set qifrecord to (get item 2 of thisStockFields) & ", " & (get item 5 of thisStockFields) & ¬
(ASCII character 13) as string
write qifrecord to tempQIFfile starting at myEOF + 1 -- append to existing data in file
set myEOF to get eof of tempQIFfile -- reset end of file to end of what is now written
close access tempQIFfile
on error -- just in case something goes wrong with write to file
close access tempQIFfile
end try
end script
(* The final line of the file has a caret ^ and a carriage return *)
script writeTail
set tail to ("^" & (ASCII character 13)) as string
try
open for access tempQIFfile with write permission
write tail to tempQIFfile starting at myEOF + 1
close access tempQIFfile
on error -- just in case something goes wrong with write to file
close access tempQIFfile
end try
end script
(* In this mainScript, 'TextEdit' opens the chosen comma delimited file *)
(* and counts the number of paragraphs in the original file. *)
(* The repeat block cycles through the odd numbered paragraphs which contain data.*)
(* It separates the data of a paragraph into a list of fields *)
(* and sends this off to the "writeToQif" script *)
(* where data we want is extracted, reformatted and written to the .txt file *)
script mainScript
tell application "TextEdit"
set pickedFile to choose file with prompt "Pick the comma-delimited download from your CommSec watch list" of type "TEXT"
open pickedFile
set numbOfParas to count paragraphs of text of document 1 of application "TextEdit"
repeat with stock from 1 to numbOfParas
if stock is greater than 2 and (stock mod 2 is 1) then
set thisStock to get paragraph stock of text of document 1
set thisStockFields to get text items of thisStock
run writeToQif
end if
end repeat
run writeTail
end tell
end script
run mainScript

Errors and inelegant scripting are my own responsibility.
This article has already been published in AUSOM News May 2005.
www.ausom.net.au
Let me know how you get on.

Back to the List of Tutorials