Java app(s) to update quotes


[UPDATED link  11/11/2017 – 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. [added 11/11/2017] Hung has reworked the app to work with Yahoo2 quote source and in addition to the quote, includes (see illustration below):
    • Trade date and time values
    • An indicator in the price column to show
    • (O) means price is from an exchange that is still OPEN
    • (C) means price is from an exchange that has CLOSED

Thank you Hung Le!

-ameridan

1157355

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.

Advertisements

471 Comments »

  1. Andy B said

    THANK YOU HUNG LE!!!! The new build (pointed to Yahoo2) works great, it did retrieve and import all my prices although the Mapper tab shows your default / example.

    I also like the date offset, easier than my old solution: reset laptop date to the day of the prices, then launch MSMoney. The import would go in dated according to the laptop date. Then, remember to correct the date!

  2. hleofxquotes@gmail.com said

    Hi Andy,
    Is the mapper functionality working but it just not showing up in the Mapper table correctly?
    If so, there is a silly DISPLAY bug that I’ve never gotten around to fix. The content of the mapper table will update AFTER you’ve done the Price update. Another words, both tables: Prices and Mapper will get update at same time.
    The mapping functionality should work despite of the above mentioned DISPLAY bug.

    • Andy B said

      Running it tonight, I get prices but the mapper tab is completely blank. Not sure what that means!

      I’m still grateful for the new new build, but . . .

      the last trade date and time are important! With mutual funds there is a zone of uncertainty between 2 and 4 hours after the market closes where you don’t know if you’re getting today’s price or yesterday’s. Some firms update their prices quicker than others, and they’re not consistent. Can this added – assuming the Yahoo2 source has it?

  3. hleofxquotes@gmail.com said

    Looking into getting the trade dates. Will fix the mapper display in next build.

    • hleofxquotes@gmail.com said

      I made progress on getting the trade date and time for Yahoo2. Next build will include the trade date and time value.

    • hleofxquotes@gmail.com said

      New build hleOfxQuotes-Build_20171110_131
      Please see README file in: https://github.com/hleofxquotes/hleofxquotes/tree/master/dist
      Fixes:

      * For Yahoo2, add Trade Date and Time values.

      * Show mapper at load time. Previously, the mapper window is only updated after the prices are updated.

      * Add an indicator in the Price column

      ** (O) means price is from an exchange that is still OPEN
      ** (C) means price is from an exchange that has CLOSED

    • Justin said

      Thanks for the update it works great!

  4. ameridan said

    Hung, let me know if I need to update any links or statements in the blog page.

RSS feed for comments on this post

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: