Java app(s) to update quotes

February 12, 2010 at 9:15 PM · Filed under Microsoft Money ·Tagged java ofx quotes, ofx downloads

[UPDATED link  1/20/2011 - the author (Hung Le) updated the Java app and the link now points to the updated version.  This is an ideal alternative for those that only need to update quotes (no Account Statement updates) and don’t wish to install Python scripting]

The first comment I received on my new Blog site is from Hung Le and he offers a Java app (hleOfxQuotes) he wrote to obtain quotes from Yahoo! and output them into an OFX file for import into Money.   If you already have Java installed, download the latest Release version of the app , save it on your PC, and just click on it.   

The program is easy to use and works great!   The first time you import the file,  just link it to a Dummy Investment Account you can create on the spot (don’t link it to a real account, since the download shows you own zero (0) shares of each so as to not have any effect on your net worth).  If you mess this up, see this Blog Page to fix the linked account.

NOTES:

1. The other accounts that actually contain these stocks and funds will automatically update, but you will not see a daily change value as you do with online updates.
2. The first download update of the day takes hold for that date, so run this after 6 or 7 pm EST if you’d like the quotes to be “closing bell” values, especially regarding mutual funds (stock values don’t have to be computed).
3. If you’d like currency exchange rates (although they work a little differently than the MSN method) or Market indexes, the method I’ve discovered and blogged here works with this app too!
4. [added 2/4/2011] Apparently the newer test version(s) also work for Options and foreign exchange investments (see comments below for more)
5. If you’d like quotes from other non-USA exchanges and other countries, the method I’ve discovered and blogged here works with this app too!
6. Keep in mind though that the Yahoo interface is a “work around” intended solely for tracking things that aren’t held in an OFX-enabled account.  If your investments are held in accounts for which you have an OFX download option, that may be the prefered method.

Thank you Hung Le!

-ameridan

For those of you that are curious as what an OFX file looks like, I’m publishing the file generated with the hleOfxQuotes app requesting quotes for Ford stock and the Dow Jones Avg this evening:

<?xml version="1.0" encoding="UTF-8"?>
<?OFX OFXHEADER="200" VERSION="200" SECURITY="NONE" OLDFILEUID="NONE" NEWFILEUID="3379335459138709546"?>
<OFX>
  <SIGNONMSGSRSV1>
    <SONRS>
      <STATUS>
        <CODE>0</CODE>
        <SEVERITY>INFO</SEVERITY>
        <MESSAGE>Successful Sign On</MESSAGE>
      </STATUS>
      <DTSERVER>20100213033831</DTSERVER>
      <LANGUAGE>ENG</LANGUAGE>
    </SONRS>
  </SIGNONMSGSRSV1>
  <INVSTMTMSGSRSV1>
    <INVSTMTTRNRS>
      <TRNUID>3965303700877776334</TRNUID>
      <STATUS>
        <CODE>0</CODE>
        <SEVERITY>INFO</SEVERITY>
      </STATUS>
      <INVSTMTRS>
        <DTASOF>20100213033831</DTASOF>
        <CURDEF>USD</CURDEF>
        <INVACCTFROM>
          <BROKERID>le.com ofx quotes updater</BROKERID>
          <ACCTID>0000-0001</ACCTID>
        </INVACCTFROM>
        <INVPOSLIST>
          <POSSTOCK>
            <INVPOS>
              <SECID>
                <UNIQUEID>F</UNIQUEID>
                <UNIQUEIDTYPE>TICKER</UNIQUEIDTYPE>
              </SECID>
              <HELDINACCT>OTHER</HELDINACCT>
              <POSTYPE>LONG</POSTYPE>
              <UNITS>0</UNITS>
              <UNITPRICE>11.12</UNITPRICE>
              <MKTVAL>0.00</MKTVAL>
              <DTPRICEASOF>20100213033831</DTPRICEASOF>
              <MEMO>Price as of date based on closing price</MEMO>
            </INVPOS>
            <REINVDIV>Y</REINVDIV>
          </POSSTOCK>
          <POSSTOCK>
            <INVPOS>
              <SECID>
                <UNIQUEID>^DJI</UNIQUEID>
                <UNIQUEIDTYPE>TICKER</UNIQUEIDTYPE>
              </SECID>
              <HELDINACCT>OTHER</HELDINACCT>
              <POSTYPE>LONG</POSTYPE>
              <UNITS>0</UNITS>
              <UNITPRICE>10,099.14</UNITPRICE>
              <MKTVAL>0.00</MKTVAL>
              <DTPRICEASOF>20100213033831</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>F</UNIQUEID>
            <UNIQUEIDTYPE>TICKER</UNIQUEIDTYPE>
          </SECID>
          <SECNAME>FORD MOTOR CO</SECNAME>
          <TICKER>F</TICKER>
          <UNITPRICE>11.12</UNITPRICE>
          <DTASOF>20100213033831</DTASOF>
          <MEMO>Price as of date based on closing price</MEMO>
        </SECINFO>
      </STOCKINFO>
      <STOCKINFO>
        <SECINFO>
          <SECID>
            <UNIQUEID>^DJI</UNIQUEID>
            <UNIQUEIDTYPE>TICKER</UNIQUEIDTYPE>
          </SECID>
          <SECNAME>Dow Jones Industr</SECNAME>
          <TICKER>^DJI</TICKER>
          <UNITPRICE>10,099.14</UNITPRICE>
          <DTASOF>20100213033831</DTASOF>
          <MEMO>Price as of date based on closing price</MEMO>
        </SECINFO>
      </STOCKINFO>
    </SECLIST>
  </SECLISTMSGSRSV1>
</OFX>

[UPDATED 1/29/2011] There are also beta test versions  posted on Hung Le’s site as well, for those wishing to experiment further, that support two additional quote sources: ft.com and Google Finance Portfolio. Hopefully, those will give our UK/Canada users additional sources for their quotes. He has also added a new mapper.csv function. Please read the Help files (Readme.txt) carefully.

In your current working dirctory, create a file named mapper.csv. It is a comma-separated-value, you can use Excel to create it but remember to save as CSV file.

mapper.csv MUST have the following header (case-sensitive, make sure spelling is correct)
MSMoneySymbol,QuotesSourceSymbol,IsMutualFund

Add row as needed
. First column: the symbol for a stock/fund in your MS Money
. Second column: the symbol that can be find in your quote source
. Third column: true if is a mutual fund

Let’s say your ft.com’s results.csv has price info for BBT:NYQ. But in MS Money the same stock has symbol BBT and this is a
stock, you would add row
BBT,BBT:NYQ,FALSE

Your results.csv use BBT:NYQ (QuotesSourceSymbol)
The generated *.ofx SHOULD have BBT (MSMoneySymbol)

---

added by ameridan from Hung Le’s comment 1/31  on Microsoft Money forum:
For currency issue: currently I do write out the OFX currency tag per security but I have no clue if MM will honor it

[SECID]
[UNIQUEID]EZJ:LSE[/UNIQUEID]
[UNIQUEIDTYPE]TICKER[/UNIQUEIDTYPE]
[/SECID]
…
[CURRENCY]
[CURRATE]1.00[/CURRATE]
[CURSYM]GBP[/CURSYM]
[/CURRENCY]

I set CURRATE to 1.00 right now but I have code to look up the exchange rate (using the pseudo-symbol USDGBP) but I have not put the code in yet because I did not want to muddle the water right now for the UK folks who are still having problem getting MM2005UK to update the price. Obviously, we can also convert from GBP -> USD before writing to *.ofx.

I want to give Google Porfolio a plug here regarding how it handles currency. For Google Portfolio, you can set the porfolio base currency and Google will report the position market value in BOTH: “native” currency and portfolio based currency. For example, let say I set my base currency to GBP and have a position of AAPL, Google will report market value in both USD and GBP. So that will take of currency conversion outside OFX generation tool. Currently hleOfxQuotes.jar can fetch data from Google Portfolio and will:

1. preserve shared count (instead of using 0 count) // this is mostly to see if it will help the UK folks

2. using “native” currency // since I don’t know how multi-currency actually works in MM

But it is fairly easy to add a couple check boxes to let user choose what he/she wants.

FYI, I am back to using MM on daily basis now (hence more active in updating hleOfxQuotes.jar). I gave Quicken a college try but things are not working out:

. I am used how the UI works and could not really overcome that

. In Quicken, if I hide the reconciled transaction, the balance is displayed certain way that is just too confusing.

I don’t mean to bash Quicken here but just a note to say that I am back to the “right” side .

 

The comments below are only the most recent – if you wish to review older comments, there is a link near the bottom of the page to do so.

About these ads

Permalink