Results 1 to 7 of 7

Thread: Convert ofx file to excel

  1. #1
    Join Date
    Apr 2009
    Posts
    105

    Convert ofx file to excel

    I had installed Windows Xp on my system. There are some files which seems to be in ofx file format. I would like to convert this ofx file to excel file. Can any body tell me that how can i convert ofx file in to excel file? Does any body knows about it? Is there any particular software for converting ofx file to excel file? Kindly provide me teh correct solution for the above issue. Any kind of help on the above issue would be appreciated.

  2. #2
    Join Date
    Apr 2008
    Posts
    4,642

    Re: Convert ofx file to excel

    The name OFX2CSV stands for OXF, QFX Converter to CSV. This utility is a small useful application to convert OFX (MS Money) or QFX (Quicken) files to CSV (Comma Separated Value) format (Excel compatible).

  3. #3
    Join Date
    May 2008
    Posts
    4,570

    Re: Convert ofx file to excel

    For doing that you can use QIF & XL to OFX Converter software on your system. Use this tool to import data into Quicken, QuickBooks, MS Money, and other OFX enabled applications.You can use the tool to create a limited number of OFX transactions, but the tool is otherwise fully functional.

  4. #4
    Join Date
    May 2008
    Posts
    4,345

    Re: Convert ofx file to excel

    The Deluxe version of MSN Money Investing lets you to export the Criteria, Comparisons Symbols and Results from Deluxe Screener to Excel, or the Results to a tab-delimited file that you can open in Excel, or any other program that reads text files. For more information you can check out the following link.

  5. #5
    Join Date
    Jan 2010
    Posts
    2

    Re: Convert ofx file to excel

    Try the following Macro:

    Sub Parse_OFX()
    Dim Acct As String, _
    TextLine As String



    filenm = "text;" & Worksheets("Sheet1").Range("b1") & Worksheets("Sheet1").Range("b2") & Worksheets("Sheet1").Range("b3")

    Worksheets("Sheet2").Activate
    Cells.Select
    Selection.ClearContents
    Range("a1").Select
    Worksheets("Sheet3").Activate
    Cells.Select
    Selection.ClearContents
    Range("a1").Select


    Worksheets("Sheet2").Activate
    With ActiveSheet.QueryTables.Add(Connection:=filenm, Destination:=Range("a1"))
    .Name = "2009-10-10 to 2010-01-03 Checking 02.ofx"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = False
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierNone
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileOtherDelimiter = ">"
    .TextFileColumnDataTypes = Array(2, 2)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    End With
    Range("a11").Select
    mrows = Worksheets("Sheet2").Rows.CurrentRegion.Count
    mrows = 900
    mcols = Worksheets("Sheet2").Range("A1").CurrentRegion.Columns.Count
    counter = 0
    For I = 1 To mrows
    Select Case Worksheets("Sheet2").Range("A1").Offset(I - 1)
    Case "<ACCTID"
    Acct = Worksheets("Sheet2").Range("A1").Offset(I - 1, 1)
    Worksheets("Sheet3").Range("A1").Offset(counter, 0) = "Account #: " & Acct
    counter = counter + 1
    Case "<TRNTYPE"
    Worksheets("Sheet3").Range("A1").Offset(counter, 1) = Worksheets("Sheet2").Range("A1").Offset(I - 1, 1)
    Case "<DTPOSTED"
    Worksheets("Sheet3").Range("A1").Offset(counter, 0) = OFX_Date(Worksheets("Sheet2").Range("A1").Offset(I - 1, 1))
    Case "<TRNAMT"
    Worksheets("Sheet3").Range("A1").Offset(counter, 6) = Val(Worksheets("Sheet2").Range("A1").Offset(I - 1, 1))
    Case "<CHECKNUM"
    Worksheets("Sheet3").Range("A1").Offset(counter, 2) = Worksheets("Sheet2").Range("A1").Offset(I - 1, 1)
    Case "<NAME"
    Worksheets("Sheet3").Range("A1").Offset(counter, 3) = Worksheets("Sheet2").Range("A1").Offset(I - 1, 1)
    Case "<MEMO"
    Worksheets("Sheet3").Range("A1").Offset(counter, 5) = Worksheets("Sheet2").Range("A1").Offset(I - 1, 1)
    Case "<REFNUM"
    Worksheets("Sheet3").Range("A1").Offset(counter, 2) = Worksheets("Sheet2").Range("A1").Offset(I - 1, 1)
    Case "</STMTTRN"
    counter = counter + 1
    Case Else
    End Select
    Next I

    End Sub
    Function OFX_Date(indate As String)
    OFX_Date = DateValue(Mid(indate, 5, 2) & "/" & Mid(indate, 7, 2) & "/" & Mid(indate, 1, 4))
    End Function

    The macro will import the QFX/OFX file into Sheet2 based on the drive, directory and file name in cells B1, B2 and B3 on Sheet1. The macro will then convert the transactions into Excel data on rows in Sheet3. The first row in Sheet3 will contain the Account #. Each record in the QFX/OFX file has a "tag" which defines what data on that record. This macro converts the following tags: date, type, check#/reference#, name, memo, and amount.

    If your bank uses additional "tags" you want to capture, you can identify the tags on Sheet2 and add code to the "Select Case" statements in the macro.

    Good Luck.

  6. #6
    Join Date
    May 2010
    Location
    Keller, Tx
    Posts
    2

    Re: Convert ofx file to excel

    Thanks for the macro, badbark. It crashes out at "End with", probably due to my lack of understanding the filename input/output aspect.

    Can you explain the following in bold:

    <CODE>
    filenm = "text;"& Worksheets("Sheet1").Range("b1") & Worksheets("Sheet1").Range("b2") & Worksheets("Sheet1").Range("b3")

    With ActiveSheet.QueryTables.Add(Connection:=filenm, Destination:=Range("a1"))
    .Name = "2009-10-10 to 2010-01-03 Checking 02.ofx"

    </CODE>

    Thanks in advance,
    Bjoern

  7. #7
    Join Date
    Jan 2010
    Posts
    2

    Re: Convert ofx file to excel

    Filenm is a string variable that is set to the CSV file name you want to import. Excel requires the parameter text; in front of the file name to specify that a CSV file is being is being queried.

    I believe that the .name isd simply the name of the range in the imported sheet.

    Hope that helps.

    You are correct that your error indicates that the macro cannot find the file

Similar Threads

  1. How to convert .prn file to Microsoft excel
    By Dalajit in forum Windows Software
    Replies: 5
    Last Post: 13-07-2011, 07:14 PM
  2. Need software to convert .DBK file to MS EXCEL file
    By Eber in forum Windows Software
    Replies: 6
    Last Post: 26-08-2010, 07:48 PM
  3. How to convert excel file to word
    By superdave1984 in forum Windows Software
    Replies: 3
    Last Post: 16-10-2009, 01:36 PM
  4. How to convert .sav SPSS file to Excel
    By Anek in forum Windows Software
    Replies: 4
    Last Post: 19-05-2009, 04:12 AM
  5. How to convert Excel file to XML format
    By Suzane in forum Software Development
    Replies: 3
    Last Post: 12-05-2009, 05:31 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,693,025.19305 seconds with 17 queries