How do I open a quicken qfx file on my mac and import it into numbers

Some while back I wrote a script that places the content of QFX/OFX files on the clipboard for easy import into Numbers 3.x. Tested with Citibank files. It may need minor modification for files produced by other banks.

Usage:

1. Copy-paste into Script Editor (in Applications > Utilities
2. Run, and choose the file at the prompt.
3. Click once in a Numbers cell, and command-v to paste.

SG

--beginning of script

(*

Purpose: Place contents of OFX or QFX financial file to clipboard for pasting into Numbers 3.

Usage: Right click a file in Finder, choose OFX to Clipboard in services, paste when done in an existing table.

Note: May need adjusting to a particular bank's format.

Another note: For big files it needs a few minutes to process.

--SGIII, v1.0b, 201402

*)

set f to choose file with prompt "Choose OFX or QFX file" of type {"OFX", "QFX"}

set tt to read f as «class utf8»

try

--remove unneeded parts and empty lines that complicate parsing

set cTxt to chopText(tt) --remove header and footer stuff

if text of cTxt contains "><" then set cTxt to addLR(cTxt) -- add line returns if ofx file omitted them

set xmlList to stripEmpties(cTxt) -- remove empty lines

--convert ofx to "true" xml format by adding closing tags

repeat with i from 1 to count of xmlList

set theItem to item i of xmlList

set itemi of xmlList to closeTag(theItem) --add closing tag

end repeat

on error

display alert "Check format of OFX or QFX file. May need to adjust script" buttons "Cancel"

end try

--write xml to temporary file -- System Events xml parser will read it from there

set xmlData to printList(xmlList)

set xmlFile to ((path todesktopastext) & "temp.xml") -- "<Computer>:Users:<username>:Desktop:temp.xml"

try

set f to open for accessfilexmlFile with write permission

writexmlDatatof

close accessf

on error

try

close accessfilexmlFile

end try

end try

display notification "Parsing " & length of xmlList & " pieces of data. May take a few minutes. There will be another notice when done." with title "System Events"

--parse the xml file with the xml parser built into System Events

set tsvString to "Amount" & tab & "Date" & tab & "Type" & tab & "Check Number" & tab & ¬

"Payee Name" & tab & "Memo" & return--header row --> set to "" if don't need headers

try

tell application "System Events"

tell XML element "BANKTRANLIST" of contents of XML file xmlFile

repeat with thisElement from 3 to (count of XML elements) -- 1 ,2 are DTSTART, DTEND - so start with 3

tell XML elementthisElement

set amount to value of its (XML elements whose name is "TRNAMT") as string

set trdate to my dateIfy(value of its (XML elements whose name is "DTPOSTED") as string)

set trtype to value of its (XML elements whose name is "TRNTYPE") as string

set checkno to value of its (XML elements whose name is "CHECKNUM") as string

set payee to value of its (XML elements whose name is "NAME") as string

set memo to value of its (XML elements whose name is "MEMO") as string

--add new line to the tsvString:

set tsvString to tsvString & amount & tab & trdate & tab & trtype & tab & checkno & tab & ¬

payee & tab & memo & return

end tell

end repeat

end tell

end tell

on error

error "System Events parser couldn't handle this one. Check format of OFX or QFX source file and adjust script. Check temp.xml (in Trash) for clues of what might have gone wrong."

end try

set the clipboard totsvString

display notification "(Finally) ready to paste " & (count of paragraphs of tsvString) - 1 & " rows into Numbers" with title "Numbers"

--finally trash the temporary file

tell application "Finder"

deletefilexmlFile

end tell

----------------handlers -----------------

to dateIfy(s) --insert - and trim so Numbers can recognize as date

set s to text 1 thru 4 of s & "-" & text 5 thru 6 of s & "-" & text 7 thru 8 of s

return s

end dateIfy

to printList(aList) -- convert list to text for writing to file

set text item delimiters to return

set printOut to aList as string

set text item delimiters to ""

return printOut

end printList

to chopText(tt) --keep only between <BANKTRANLIST> AND </BANKTRANLIST>

set oTid to AppleScript'stext item delimiters

set AppleScript'stext item delimiters to "<BANKTRANLIST>"

set tt2 to "<BANKTRANLIST>" & text item 2 of tt --chop off head

set AppleScript'stext item delimiters to "</BANKTRANLIST>"

set tt2 to text item 1 of tt2 & "</BANKTRANLIST>" --chop off tail

set AppleScript'stext item delimiters to oTid

return tt2

end chopText

to addLR(tt) --add returns if omitted in compressed ofx

set tt to findReplace(tt, "<", "

<")

end addLR

to stripEmpties(tt) -- text in, list out

set ttParas to paragraphs of tt--loads each line as a list item

set xmlList to {}

repeat with i from 1 to count of ttParas

set thisPara to itemi of ttParas

set isNotEmpty to text of thisPara is not ""

if isNotEmpty then set end of xmlList to thisPara

end repeat

return xmlList

end stripEmpties

to closeTag(s) --add closing tag omitted in ofx

if item -1 of s is not ">" then --if already closed

set oTid to AppleScript'stext item delimiters

set AppleScript'stext item delimiters to ">"

set oTag to text item 1 of s & ">"

set tagValue to text item 2 of s

set cTag to "</" & text 2 thru -1 of oTag

set s to oTag & tagValue & cTag

set AppleScript'stext item delimiters to oTid

return s

else

return s

end if

end closeTag

on findReplace(tt, f, r)

set oTid to AppleScript'stext item delimiters

considering case

set AppleScript'stext item delimiters to f

set lst to every text item of tt

set AppleScript'stext item delimiters to r

set tt to lst as string

end considering

set AppleScript'stext item delimiters to oTid

return tt

end findReplace

--end script

It occurs to me that you might have a Citibank bank account and not a Citibank credit card for the current transactions that you want to download. As I personally do not have a bank account with Citibank, I can not log on and simulate your situation.

If you are truly limited to only two download choices, which includes QFX for Quicken, I suggest you purchase the qfx2csv converter from MoneyThumb:

https://www.moneythumb.com/convert-to-spreadsheets/

They have many differerent converters for both Mac and Windows.

I use two of their other converters and they are lifesavers for my monthly imports into Quicken 2007 for Mac, including American Express, which earlier last year eliminated QIF as an export option! So I convert from csv to qif.

t quinn wrote:

I don't know why that was hard for you.

Wayne indicated that Numbers '09 version 2.3 would open QFX files, but version 3.5 no longer contains that capability.

I indicated that I would like to download and try out Numbers version 2.3.

You now, for the first time on this thread, indicate that Numbers '09 version 2.3 is part of iWork '09 and both of those downloads you posted are links to iWorks '09 that you found by doing a Google search for iWorks '09

Do you really have to explain at any more length why that was hard for me?!?

Thank you for the download links to iWorks '09! Wouldn't it have just been much easier for both of us for you to just post those links in your first post and not imply I was ignorant of how to use Google?

You can use Numbers 2.3 (Numbers '09). There is a free, 30 day trial on the internet and you can purchase install media from Amazon. I kept my install of iWork '09 just for this purpose.

Wayne Contello wrote:

you have to use Numbers '09. This was dropped in Numbers 3.x

Indeed, using the trial version of Numbers 2.3, it easily opens my QFX files into easily readable spreadsheets! 🙂

Good to know that this tool exists. Since I already purchased two of the MoneyThumb converters for my purposes (importing into Quicken 2007 for Mac), it is doubtful I will have a need to purchase the license for Numbers '09.

Citicards supports the download of CSV format, which will import into most spreadsheet progams easily.

Are we looking at the same place on the Citicards site?

After clicking on Download Transactions, I see:

Now that you pointed out it is current transactions you want to download ("Since Last Statement"), I see many more options, including CSV which will directly import into most spreadsheets, including, I assume, Numbers!

you have to use Numbers '09. This was dropped in Numbers 3.x

Similarly, as noted in Lonnie West's blogpost about his script for QFX to Excel, that I linked in an earlier post:

For two and a half years I have received countless emails from readers who struggle with this process. While the steps are simple, the nature of the QFX format itself, the variance in banking systems’ formatting standards, and even the operating systems and text editors used all can cause the result to be a mal-formed XML file.

SGIII wrote:

That different institutions seem to like to introduce customizations into their OFX/QFX files, confounding amateur scripts, means that MoneyThumb and others will continue to fulfill a need!

Indeed, as I had a personal need to use my Debit card countless times this last summer, the purchase of MoneyThumb's converter saved me hours of manually typing all of these debit transactions into Quicken!

For security reasons, I decided to have my Debit card replaced and now I use it very sparingly!

For similar reasons I NEVER use Quicken to log into any of my personal financial institutions. I do it manually each time through a secure web browser and then import the QIF information into Quicken -- shades of avoiding Target, Anthem and now Uber driver database break-ins! 😠

That different institutions seem to like to introduce customizations into their OFX/QFX files, confounding amateur scripts, means that MoneyThumb and others will continue to fulfill a need!

SG

SGIII wrote:

Some while back I wrote a script that places the content of QFX/OFX files on the clipboard for easy import into Numbers 3.x. Tested with Citibank files. It may need minor modification for files produced by other banks.

Usage:

1. Copy-paste into Script Editor (in Applications > Utilities
2. Run, and choose the file at the prompt.
3. Click once in a Numbers cell, and command-v to paste.

SG

--beginning of script

...

I would like to experiment with your script, but I cannot find Script Editor in Utilities on my OS X Lion Mac.

UPDATE: I found AppleScript editor in Utilities...

Hi quinn,

I think a key thing in that particular Google search is to include "trial." I understand Google presents different search results to different people (depending, among other things, on where they are). Here, if I don't include "trial", the results (at least the top hits) don't include the useful links. Though Wayne had the word trial in his prior post, that word's significance in a Google search may not have been entirely clear at the time of your first post. Michael had no easy way of knowing you had previously posted on several occasions the helpful tip about a trial being available for download.

SG

Look for AppleScript Editor in Applications > Utilities (or Spotlight search). Apple used to call it Script Editor, then changed the name to AppleScript Editor, and has changed it back in recent versions of OS X because it's now often used for JavaScript too.

Note that the script saves a temporary file and then sends it to the trash. So you'll want to exercise care if more than one person has access to your machine.

SG

Yes, the security aspects are worrying. Downloading an unencrypted text file (OFX/QFX/QIF or whatever) is perhaps no longer such a great thing to be doing, though I assume it occurs over a secure connection. ... OFX, at least the Citibank flavor, is clearly malformed XML. I found that out when I realized that on our Macs we all have a built-in XML parser associated with "System Events." Very nice. But that parser is *very* fussy, requiring lots of pre-processing in AppleScript to get the format just so. Good to have Lonnie West's post, because in a pinch I would guess Excel's XML import may be more forgiving.

SG

Wayne Contello wrote:

You can use Numbers 2.3 (Numbers '09). There is a free, 30 day trial on the internet...

Can you please provide that link? I would like to try it out. I can only find a link for v3.5