Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
QIF Import into Sunset for Investment Price Update? RRS feed

 > 

  • General discussion

  • Discussion
    Sign in to vote
    0
    Sign in to vote

    Was trying to import some QIF into an investment account as follows:

    !Type:Invst
    D11/27/10
    YBARC.L
    I11.24
    ^

    Money doesn't seem to let you even bring this into an investment account. And when you bring it into the associated cash account it erros with invalid/corrupt data.

    I was only doing this to see if the QIF update method would kick the 'last updated' column into working (OFX doesn't)

    Anyone know if QIF import to an investment account is even possible or have an examples?

    I've also tried adding the following header:

    !Account
    NJoint Brokerage Account
    TInvst
    ^

     

    Saturday, November 27, 2010 8:26 AM

All replies

  • Discussion
    Sign in to vote
    0
    Sign in to vote

    If you want to see what a QIF file should look like for an investment account, File->Export a loose QIF file from such an account.

    Use File->Import->RecoverAccounts to read your QIF file for an investment account.

    Saturday, November 27, 2010 4:05 PM
    Moderator
  • Discussion
    Sign in to vote
    0
    Sign in to vote

    Many Thanks

    Doesn't seem to like price updates only. This still gets the same error:

    !Type:Invst
    D27/11'2010
    T0.00
    NShrsIn
    YBARC.L
    I55.50
    Q0
    ^

    As does:

    !Type:Invst
    D27/11'2010
    YBARC.L
    I55.50
    ^

    Oh well. Was worth a punt!

     

    Saturday, November 27, 2010 4:16 PM
  • Discussion
    Sign in to vote
    0
    Sign in to vote

    You could try a Buy of zero shares. Still, it's messy. An OFX file is cleaner because it does quotes directly.

    Saturday, November 27, 2010 4:21 PM
    Moderator
  • Discussion
    Sign in to vote
    0
    Sign in to vote

    Here is a tested example. Import into an investment watch account with File->Import->RecoverAccounts. QIF file dates are interpreted according to the regional settings in Windows. I set the date to Nov 11 to allow the date to be accepted whether Windows is set to mm/dd/yyyy or dd/mm/yyyy. The price is fake 55.123. I notice that the http://uk.finance.yahoo.com/q/hp?s=BARC.L site has the price in pence. I chose a number close to your test number. I carried the fake price to 3 digits to distinguish it from real prices.

    I would probably be best to get rid of the "Mtesting quotes" lines to save space in the Money files.

    !Type:Invst
    D11/11'2010
    CX
    Mtesting quotes
    T0.00
    NBuy
    YBARC.L
    I55.123
    Q000
    ^

    Here is a mm/dd/yyyy example of a second QIF file:

    !Type:Invst
    D11/12'2010
    CX
    Mtesting quotes
    T0.00
    NBuy
    YBARC.L
    I55.456
    Q000
    ^
    D11/13'2010
    CX
    Mtesting quotes
    T0.00
    NBuy
    YBARC.L
    I55.789
    Q000
    ^

    Here is dd/mm/yyyy example of a second QIF file:

     !Type:Invst
    D12/11'2010
    CX
    Mtesting quotes
    T0.00
    NBuy
    YBARC.L
    I55.456
    Q000
    ^
    D13/11'2010
    CX
    Mtesting quotes
    T0.00
    NBuy
    YBARC.L
    I55.789
    Q000
    ^

    • Edited by Cal LearnerModerator Saturday, November 27, 2010 5:26 PM added more test QIF file contents
    Saturday, November 27, 2010 5:13 PM
    Moderator
  • Discussion
    Sign in to vote
    0
    Sign in to vote

    Hi Cal,

    Many thanks for the examples. I still cant get it to import for some reason'- but did yours manage to update the 'last updated' field in portfolio manager? That was the reason behind the experiment.

     

    Sunday, November 28, 2010 3:15 AM
  • Discussion
    Sign in to vote
    0
    Sign in to vote

    Hi Cal,

    Many thanks for the examples. I still cant get it to import for some reason'- but did yours manage to update the 'last updated' field in portfolio manager? That was the reason behind the experiment.

     


    I would have not guessed that. I figured it was because OFX files were more complex.

    No. The process updated the LastTransaction column, and left the LastUpdated column blank (since I had not ever gotten quotes for that from the quote server).

    Sunday, November 28, 2010 6:05 AM
    Moderator
  • Discussion
    Sign in to vote
    0
    Sign in to vote
    and LastTransation was just a price update? Or added shares/recorded a buy etc?
    Sunday, November 28, 2010 10:37 AM
  • Discussion
    Sign in to vote
    0
    Sign in to vote
    and LastTransation was just a price update? Or added shares/recorded a buy etc?

    A zero-share buy as described in the QIF file.
    Sunday, November 28, 2010 3:23 PM
    Moderator
  • Discussion
    Sign in to vote
    0
    Sign in to vote

    One option to use this field to see a price has indeed been updated. However I guess people also check LastTransaction against Dividend Payment dates etc so maybe not the best use.

    Real shame we cannot kick 'LastUpdated' into life as we would then lose nothing!

    Sunday, November 28, 2010 4:26 PM
  • Discussion
    Sign in to vote
    0
    Sign in to vote

    I won't miss LastUpdated much. Since I am triggering the PocketSense scripts ,which get the quotes and OFX, myself, I pretty much know when things were updated.

    What I will miss the most from the Portfolio are the Change and %change columns. Total return for the week is the shortest duration indicator remaining.

    Sunday, November 28, 2010 4:40 PM
    Moderator
  • Discussion
    Sign in to vote
    0
    Sign in to vote

    I have been playing around with QIF imports to see if they can provide a solution to my problem of importing historical prices.

    I have found that the minimum QIF record structure needed to import is:

     

    !Type:Invst
NShrsIn
D25/03/2010
I0.98
^
NShrsIn
D26/03/2010
I0.95

    




<<etc.....>>

    where the last three lines are repeated for every date / price combo you wish to import.

    You do not need to supply the investment name, or any other data. As you go through the import process (File > Import > RecoverAccounts) you get asked which investment to match to the transactions.  Note also the date format: it can use slashes to delineate the year rather than a comma.  Importing is this way defaults to an 'Add' transaction of zero shares on the date and at the price specified.

    If you code 'NBuy' rather than 'NShrsIn' the 'Last Transaction' field is updated.  It take the value of the latest price imported.  'NShrsIn' does not update that field.  'NBuy' also creates a 'Buy' activity, rather than 'Add shares'.  I have not found a way of updating 'Last Updated'.

    You would need to create a separate QIF import file for each investment you wished to update.  I need to work on an Excel macro or similar to convert a list of prices / dates into this format.

    The obvious disadvantage of this approach is that it creates a whole pile of new transactions, one for each price update, rather than just updating the price file.

    The search to find a more elegant way of just updating the price history file goes on.

     

    Monday, November 29, 2010 2:27 PM
  • Discussion
    Sign in to vote
    0
    Sign in to vote

    The search to find a more elegant way of just updating the price history file goes on.

     


    The more elegant way would be with an OFX file.

    Try the PocketSense scripts. http://pocketsense.blogspot.com/ After doing so, look at the .\xfr folder for the quotes*.ofx file for a good quotes example. Play with the dates in <DTPRICEASOF> record to see if you can have records for the same stock, each with different dates and prices. I have not tried that.

    Monday, November 29, 2010 4:33 PM
    Moderator
  • Discussion
    Sign in to vote
    0
    Sign in to vote

    I am starting to followup on this helpful suggestion, however I am struggling somewhat.  Whenever I use Notepad or Wordpad to open the OFX file, somehow it gets corrupted and Money wont let me import it (I get the 'corrupt file' error message) even if I make no changes.  Another complication is that the file is all on one line, so it makes it very confusing to follow, but even so I think I can understand the syntax of the file, even though I am no programmer, well enough to play around with trial and error.  But firstly I need an editor.

    Can someone suggest an editor for the OFX files generated by pocketsense ?

    Thanks

    Angus

    Saturday, December 4, 2010 2:26 PM
  • Discussion
    Sign in to vote
    0
    Sign in to vote

    Can someone suggest an editor for the OFX files generated by pocketsense ?

    OFX version 1.x (used by PocketSense) is a form of SGML. The files that you see in the xfr folder are actually what came in from the financial institution. SGML ignores lines for the most part, but most editors need lines (carriage returns and/or line feeds) to work well. I searched for "SGML editors" but nothing felt quite right.

    http://fi.intuit.com/ofximplementation/dcsteps/index.cfm?view=dcdevelop lists Helper Applications including OFX Formatter: This is a tool that reads in an OFX file (including an ofxlog.txt file) and adds carriage returns, line feeds and tabs to make it more readable. It creates a file named *.tab that you can view or edit in your favorite editor. The editor I use most often will let me replace all '<' characters with a newline sequence followed by a  '<' . The "OFX Formatter.exe" can be used as a drag-and-drop tool or as a command line tool if you prefer. If used as a command line tool, I would rename the exe file to something shorter without spaces. The application does not need to be installed. It is a stand-alone utility. It can be used with OFX 1.x and OFX 2.x.

    The "OFX Viewer Tool",  referenced on the web page, did not impress me as a viewer when I briefly tried it. Maybe you would find it useful. It appears to be more of a syntax checker and debugger.

     

    Saturday, December 4, 2010 4:18 PM
    Moderator
  • Discussion
    Sign in to vote
    0
    Sign in to vote

    A sample OFX v2 for quote (Cisco). What I generated from hleOfxQuotes.jar (http://www.le.com/~hle/out/hleOfxQuotes/readme.txt)

     

    <?xml version="1.0" encoding="UTF-8"?>
<?OFX OFXHEADER="200" VERSION="200" SECURITY="NONE" OLDFILEUID="NONE" NEWFILEUID="152069791534795545"?>
<OFX>
 <SIGNONMSGSRSV1>
  <SONRS>
   <STATUS>
    <CODE>0</CODE>
    <SEVERITY>INFO</SEVERITY>
    <MESSAGE>Successful Sign On</MESSAGE>
   </STATUS>
   <DTSERVER>20101229155447</DTSERVER>
   <LANGUAGE>ENG</LANGUAGE>
  </SONRS>
 </SIGNONMSGSRSV1>
 <INVSTMTMSGSRSV1>
  <INVSTMTTRNRS>
   <TRNUID>6113590567097127814</TRNUID>
   <STATUS>
    <CODE>0</CODE>
    <SEVERITY>INFO</SEVERITY>
   </STATUS>
   <INVSTMTRS>
    <DTASOF>20101229155447</DTASOF>
    <CURDEF>USD</CURDEF>
    <INVACCTFROM>
     <BROKERID>le.com</BROKERID>
     <ACCTID>0123456789</ACCTID>
    </INVACCTFROM>
    <INVPOSLIST>
     <POSSTOCK>
      <INVPOS>
       <SECID>
        <UNIQUEID>CSCO</UNIQUEID>
        <UNIQUEIDTYPE>TICKER</UNIQUEIDTYPE>
       </SECID>
       <HELDINACCT>OTHER</HELDINACCT>
       <POSTYPE>LONG</POSTYPE>
       <UNITS>0</UNITS>
       <UNITPRICE>20.38</UNITPRICE>
       <MKTVAL>0.00</MKTVAL>
       <DTPRICEASOF>20101229155447</DTPRICEASOF>
       <MEMO>Price as of date based on closing price</MEMO>
      </INVPOS>
      <REINVDIV>Y</REINVDIV>
     </POSSTOCK>
    </INVPOSLIST>
   </INVSTMTRS>
  </INVSTMTTRNRS>
 </INVSTMTMSGSRSV1>
 <SECLISTMSGSRSV1>
  <SECLIST>
   <STOCKINFO>
    <SECINFO>
     <SECID>
      <UNIQUEID>CSCO</UNIQUEID>
      <UNIQUEIDTYPE>TICKER</UNIQUEIDTYPE>
     </SECID>
     <SECNAME>Cisco Systems, In</SECNAME>
     <TICKER>CSCO</TICKER>
     <UNITPRICE>20.38</UNITPRICE>
     <DTASOF>20101229155447</DTASOF>
     <MEMO>Price as of date based on closing price</MEMO>
    </SECINFO>
   </STOCKINFO>
  </SECLIST>
 </SECLISTMSGSRSV1>
</OFX>

     

    Wednesday, December 29, 2010 3:59 PM
  • Discussion
    Sign in to vote
    0
    Sign in to vote

    Have a look at this link to see if this program may be helpful to you.

    http://www.xs4all.nl/~csmale/mt2ofx/en/index.htm
    Friday, January 7, 2011 1:02 AM