Advocacy

  Myths
  Press

Dojo (HowTo)

  General
  Hack
  Hardware
  Interface
  Software

Reference

  Standards
  People
  Forensics

Markets

  Web

Museum

  CodeNames
  Easter Eggs
  History
  Innovation
  Sightings

News

  Opinion

Other

  Martial Arts
  ITIL
  Thought


Quicken-Script
Add financial information to quicken automatically


By: Tim Pedone

Well, I just finished installing my new copy of Quicken 98 for Macintosh and I'm in financial bliss. I can keep track of everything: My investments, my credit cards, my checking account, etc. Then I notice that Quicken allows you to import stock prices. This is great! Since I have a portfolio on Yahoo! Finance, I can now use the "Export to Spreadsheet format" option to export my stock prices to a file, then import those prices into Quicken.

Well, if life were only that easy. As it turns out, the exported file is close to what Quicken wants but just a little off. Quicken wants a file in the following format:

!Type:Prices
"AAPL",20.16,"4/24/1998"
^

while Yahoo! export gives you:

"AAPL",20.16,"4/24/1998","5:52PM",-0.30,N/A,N/A,N/A,N/A

Notice the "!Type:Prices" at the top and the caret at the bottom of the Quicken format. Sounds like a job for AppleScript.

I decided it wouldn't be too tough to write a little script that would create the proper file for me. So, here's what I did.

Disclaimer: I wrote this script for my own use. I basically wrote it on the fly, not trying to account for all possible errors, or attemping to do any kind of initial design at all. It is not an example of well written code! The author accepts no responsibility for it "eating your data" or for it causing any emotional stress. Use at your own risk!

The first thing I noticed was that the downloaded file was not of type 'TEXT' so I couldn't read it with SimpleText or some other text editor. So, first let's change its to type 'TEXT'. Let's start off with an open file handler (so we can just drop the file on the script) and tell the Finder to set the type of each file to 'TEXT'

on open fileList
-- set type to text so we can easily read it in a text editor
repeat with oneFile in fileList
  tell application "Finder"
    set file type of oneFile to "TEXT"
  end tell

Now, we need to copy the file we don't mess up the original and setup some variables for later. First we have a boolean variable isClosed so later we can tell if we have closed the file or not. I did this basically so I can tell during error handling if the file was closed before the error occured or not. theCount is used as an index in our parsing loop. newFile will contain a reference to the copied file. It is this file that we will be modifying. myFile is the original file that we will be reading from. After opening myFile, we duplicate it setting newFile to the result.

-- initialize variables
set isClosed to false -- has the file been closed.
set theCount to 1 -- index
set newFile to "" -- the copy of the file we'll be writing to.
 
-- copy the original file so we don't touch the original
set myFile to open for access oneFile
tell application "Finder"
  duplicate oneFile
  set newFile to result
end tell

OK, we've got a new file to write into, we must open the file, clear it and then write the proper data into it. First, well open the file for write access, set its end of file (EOF) to 0 and write the header info that Quicken wants

-- open the copy so we can write into it.
set newFile to open for access newFile with write permission
 
-- clear the file
set eof newFile to 0
 
-- write the header
write "!Type:Prices\r" to newFile

Now comes the real work. We've got to go through the first file (myFile) and determine what should be written into the new file (newFile). To do this, we can read the entire contents into a list using the comma and carraige returns as delimiters. We can then iterate through the list using a "repeat with x in list" loop. If you look at the export format, you'll notice that we really only need the first 3 items: The stock symbol, the current price and the date. So, if theCount is greater than 3, we don't to anything. If the count is less that or equal to 3, we write the data into the new file. For all but the first line, we will encounter a line feed character that we don't want. If the first character in any string is a line feed, we need to take out the line feed. If the count is 3, we want to write the a carraige return to the file, if not, write a comma to separate the items. Finally, theCount is 9, we're at the end of an stock, so reset theCount to 1 for the next stock. After iterating through, we need to write in a terminating caret and carraige return.

try
-- read the file into myList
set myList to (read myFile using delimiters {",", "\r"} as list)
 
-- iterate through the list
repeat with x in myList
 
-- we need to skip over all but the first 3 items
if (theCount 3) and length of x as string > 1 then
  -- get rid of that pesky line feed.
  if (the first character in x = (" ")) then
    set charCount to (length of x)
    set y to characters 2 thru (charCount) of (x as string)
    write (y as string) to newFile
  else
    write x to newFile
  end if

  if (theCount = 3) then

    write "\r" to newFile
  else
    write "," to newFile
  end if
end if
 
if (theCount = 9) then
-- we're at the end of the line.
  set theCount to 1
else
  set theCount to theCount + 1
end if
 
end repeat
-- terminate with a caret
write "^\r" to newFile

Of course, we also need to handle errors. If we get an error, close the files, then display a dialog to the user. Make sure to set isClosed to true so we don't try to close it later.

on error msg
  close access myFile
  close access newFile
  set isClosed to true
  display dialog msg
end try

Check to see if we closed the files in the error handler. If not, close them now.

if not isClosed then
  close access myFile
  close access newFile
end if
 
end repeat
 
end open

That should just about do it. With that you can download quotes from Yahoo, and convert them to Quicken format and bring them into Quicken.

Download Script (as Applet)

QuoteImporterApp.sit


Created: 05/27/98
Updated: 11/09/02


Top of page

Top of Section

Home