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