Update December 29 2017 –  SunsetOFX now supports importing Yahoo quote exports.  This isn’t as fast as the old direct downloading of quotes but it sure beats manual entry and I hope you are able to realize some time savings with this update.  The new version can be downloaded here.  To use this functionality, download an updated quotes.csv file from your Yahoo portfolio screen by clicking “Export” there.  In SunsetOFX, make sure the “Yahoo CSV” tab is selected on the left (the other option is “Manual Input”). Click the “Load Yahoo CSV” button and open the quotes.csv file you downloaded from Yahoo.  Sunset OFX will go through that file, and where it sees a ticker in the file that exists in your SunsetOFX ticker list, it will update the price and date.  A couple things to note… 1) if a ticker in the quotes.csv file is not in your SunsetOFX list, it will be ignored and will NOT be imported as a new ticker for SunsetOFX to process for Microsoft Money import, 2) as with any SunsetOFX update, I recommend you back up your tickers.xml file (from wherever you are running SunsetOFX on your PC) and your Microsoft Money data file, 3) please provide feedback if you run into issues… this is freshly-developed and I don’t know enough about the particulars of Yahoo’s CSV output to have much error-handling in place. 

Update November 2 2017 –  It appears Yahoo has discontinued the web service that SunsetOFX and Perl/Java scripting solutions depend on to download ticker quotes.  The scripting solutions may be dead in the water, but with SunsetOFX you can still manually update your quotes for importing into MS Money.  Not as easy as it was October 31, but definitely easier than one-by-one price updates within MS Money.  If other solutions develop, or if Yahoo changes their mind, I’ll update this post.

Update July 19 2016 –  I noticed when Yahoo Finance online access goes flaky while you are updating quotes, an unfriendly error message pops up.  Now you should see a note about a quote lookup not making it back and to try again. Current version is 1.0.1.9 and is what you’ll get from the Downloads page.

Update March 1 2015 –  After using SunsetOFX for a couple of years for all my own quote updates for Microsoft Money, I have brought it out of beta into Version 1.n.  Thank you for all the feedback!

This post is mainly for those using python scripts and other methods to supply the sunset edition of Microsoft Money with stock quotes from Yahoo.

Lately the quotes coming down from Yahoo have contained odd numbers for prices, and invalid dates in the quotes. This prevents a valid OFX file from being created.

Inspired by http://thefinancebuff.com , the creator of the Python script I have been using to get my stock price updates nightly, I created a Windows app that lets you establish a ticker list that you can add/delete from, as well as visually change what is being downloaded from Yahoo’s web services before the OFX file is created. That way if any stock prices are showing $554,665,321 when it should be $10.23, you’ll be able to change them without hand-editing an OFX file or manipulating Python script.

If you’d like to give it a try you can download the tool at http://syntap.com/downloads/sunsetofx.zip. Unzip the file to its own directory and run the program, no other installation required. Whenever you exit the app your tickers will be saved to an xml file. Also in that directory is where the OFX file the app creates will be stored, filename SunsetOFX.ofx.

When beginning to use this tool, it is highly recommended that you try it out on a copy of your MS Money data file in order to make sure it is working with your setup.

Remember, the assumptions for this app are the same as the thefinancebuff.com Python script discussed here. When you add tickers, you get a choice between Stock and Fund. Make sure you specify the same thing your MS Money file is expecting. To check that within Money, right-click on a portfolio position and select “Investment Details” to see what the “Investment type” field is showing.

The .NET framework required is 3.5, which comes default with Windows 7.  The latest version is in the file linked above.

There is some support for options at this time, but quotes are not automatically downloaded.  When you create your option position in Money, use the Yahoo ticker as the position name and do not supply a ticker symbol.  In the Dummy Investments area, as with a stock or fund entry, “buy” zero contracts of it.  When updating quotes, manually fill in the latest option price and it will be included in the OFX dump.  If you run into trouble where Money option prices are not updating, look at the holding settings and copy-paste the option symbol into the Ticket field for the option in SunsetOFX.  I run into this problem myself sometimes because Money supports the old option ticker symbol system which was a shorter ticker string.  If it is truncated in Money, just paste into SunsetOFX whatever Money is showing for the ticker.

 

SunsetOFX Screen Capture

 

41 Responses to New OFX File Creator Tool – SunsetOFX

  1. Don says:

    When I manually enter a stock price for AAPL and a date of 12/30/2016; Microsoft Money will update the price with today’s date. I was expecting it to update the price for 12/30/16. Is this a bug? Am I doing something wrong.

    • Bill says:

      Hi Don, A number of factors can end up with that result… Money’s OFX importing seems to have a number of peculiarities, including not reflecting a price update if an OFX file was already applied that day and other factors. I have not attempted to use OFX importing to populate a position’s past price history and didn’t have that as a feature goal when building it… if SunsetOFX isn’t helping there I would use Money’s built-in methods for applying price history changes.

      SunsetOFX was built to give as close a result as possible to the pre-Money-sunset days of online quote updates where you’d hit a button and have the current prices updated. The way Money seems to work with OFX files has told me over time that the best way to apply them is first to load a Yahoo quotes.csv download into SunsetOFX and apply to MS Money only after Yahoo is showing all quotes for the day are finalized (usually after 6:15pm Eastern when accounting for mutual funds), that after that apply any buys/sells you had that day.

      The ability to adjust dates in SunsetOFX is there for cases such as running a quote update on a Saturday or Sunday and want the dates to reflect Friday (though importing Yahoo .csv files seem to apply the correct dates), or for hand-entering prices and need to apply the date field.

      • Don says:

        Thank you for pointing out the once daily OFX update peculiarity of Money. This allowed me to tweak some things that in turn allowed me to put in a group of historical quotes into Money. I have enough securities that this saves me the time of typing in manually. Here’s my procedure:
        I export a .csv file from Yahoo Finance with my securities. Open in a spreadsheet and sort the symbols list alphabetically. Copy the list and paste into a Google Docs Spread sheet in Column A. In Cell B1 put the date you want historical quotes from. In the rest of column B use the following function =index(GoogleFinance(A2,”close”,B$1),2,2)
        Copy the results and paste back into the .csv file from Yahoo in the Current Price column. Change the date in the Date column to that which you used in the Google Spreadsheet. Change the time to 16:00 EST in the time Column. Delete all the data in the other columns- open, high, low… Save this file as .csv. Open SunsetOFX. Click Load Yahoo CSV and choose the file you just saved. The go to the Manual Input tab and enter the historical date you have chosen and click Apply Date. Then click on Export OFX. The file is saved to the folder where SunsetOFX is installed as SunsetOFX.ofx. Edit this file in Notepad or other text editor. Find and change the date to the historical date you have been working with, using the format YYYMMDD. Save this file and then double click to import into Money or go back to SunsetOFX and click Launch OFX (open Money first).
        Bill, if you felt so inclined in the future to have the Apply Date go into the date for that would save editing the .ofx file.
        If anyone knows how to get a historical quotes list from Yahoo instead of the latest price, please share. That would streamline this procedure.
        Thank you Bill for the nice app!

        • Bill says:

          Great, I’m glad you got it worked out! I’ll take a look at the date field you are referring to.

          To get historical quotes you may want to look into AlphaVantage. That was a spot I investigated for getting quote data when Yahoo’s previous method for updates was deactivated.
          From what I remember it didn’t work out because they were slanted toward historical data and not current stock price in an easy way for me to grab it.
          https://www.alphavantage.co/

  2. Bill says:

    Hi all, I noticed as of yesterday Yahoo is having trouble returning stock quotes. The Yahoo forums have notes from their engineering team that this is an issue they are working on, as opposed to the service being discontinued.

    So for now we wait until they have restored service, in the meantime you can use the graphic interface of SunsetOFX to manually update your quotes for MS Money importing. Thanks! – Bill

    • Bill says:

      Update: now it appears Yahoo is discontinuing the quote download functionality that SunsetOFX and pretty much all the scripting solutions depend on. This is bad news, but not absolutely fatal.

      As mentioned in my last post, one can use SunsetOFX to manually update quotes and then move them into MS Money, which puts its users in a better position than the scripting solutions, which may be totally SOL.

      I will be on the lookout for other quote services that I can integrate into SunsetOFX in the future. For now, personally, I will likely update my quotes in SunsetOFX manually on a weekly basis instead of the simpler daily download I’ve been used to. Hopefully Yahoo changes their mind.

      The Yahoo admins posted this regarding the functionality (not to me specifically, but to the public for all tools that use the quote downloading functionality):

      “UPDATE: It has come to our attention that this service is being used in violation of the Yahoo Terms of Service. As such, the service is being discontinued. For all future markets and equities data research, please refer to finance.yahoo.com Thank you.”

      • Mark says:

        If a new quote service isn’t found, would it be possible to build an import function into SunsetOFX? In finance.google.com, one can build a portfolio with the tickers needed, then copy/paste into excel. Given a .csv template with SunsetOFX’s five columns it would be far faster than manually typing in all the quotes and dates. Especially for those of us with a large number of equities. Thanks again for this great utility!

        • Bill says:

          I think the answer is “we’ll have to wait and see”… if you go to Google Finance now it says “Google Finance is under renovation. As a part of this process, the Portfolios feature won’t be available after mid-November 2017. To keep a copy, download your portfolio. ”

          I’m guessing the CSV download functionality will go away if the whole portfolio tracking is going away, but we’ll see. If their revamp gives me something to work with I’ll definitely give it a shot.

          You don’t have to type in dates for every quote the way I have modded SunsetOFX, you can set a date to apply to all the quotes.

          What I have done for myself is set up a new Yahoo portfolio view with all my quotes in alphabetical order. I load SunsetOFX and sort the quotes by ticker (click Ticker column header), then spreadsheet-style I type in the quote amount, hit enter, then type the next one. In my case I have around 50 quotes and takes about a minute to get the grid updated that way.

    • would it be possible to setup a comma delimited file import? if we setup a portfolio in Yahoo and export it in csv format, could we import it into your Sunset OFX program to update Money??? This is a great tool and I miss it!!!

      • Bill says:

        Possibly… a way to do it may be to have SunsetOFX read an exported CSV from Yahoo and update prices from there where the tickers match the list in SunsetOFX. I wouldn’t want to to a straight import and pull in new symbols because MS Money needs to keep stocks vs funds straight. I’ll give that method a look.

      • Bill says:

        Update – I added support for this and made the new version available yesterday evening, notes are in the original post. Let me know how it works out! I don’t miss manually entering prices, the quotes csv import is a lot quicker and less error-prone.

  3. tom says:

    I have been using Money 2000 Canadian version since 2000. It is working on Windows 8.1 but I couldn’t get it to work on Windows 10. Anybody have any success with Windows 10?

  4. Rick says:

    I have been using the SunsetOFX tool for over a year. I haven’t had problems, but it has quit working now. I have tried it on Windows 7 and XP. Just wondering if others are have the same problem.

    • Bill says:

      Rick – sorry you are running into trouble. What area of it is not working for you now? Retrieving quotes, or the program won’t load at all? What version of it are you running? For what it is worth I just ran it in Windows 7 and it pulled down a list of sample quotes I put in.

      • Rick says:

        I have 3 versions – 0.9.4.0, 0.9.4.4, and 1.0.1.4. I was using 0.9.4.4 when the problem started, I couldn’t download quotes. I downloaded 1.0.1.4 and tried it but got the results. I deleted the program and tried to start over. I am now able to download but when I exit the program the ticker file is not saved. Also I can’t export to Money.

        • Bill says:

          Hi Rick, did SunSetOFX create a “SunsetOFX.ofx” file in the directory where you ran it after you hit the Export OFX button? If that file didn’t create and your tickers didn’t save (they are in file “tickers.xml”) then maybe there is a file permissions issue but we’d expect to see error messages of some kind.

          If the SunsetOFX.ofx file did get created but the “Launch OFX” button didn’t work for you, the OFX file can still be imported from within Money.

          • Rick says:

            I do receive the following error using version 0.9.4.0 “Access to the path C:\\windows\system32\SunsetOFX.ofx is denied”. I do not get any errors on versions 0.9.4.4 or 1.0.1.4, but they do not save anything.

            • Bill says:

              It could be something in Windows preventing file writes in the system folder. SunsetOFX writes files to the directory the executable is sitting in. Maybe moving the executable to somewhere like My Documents where application file writes would be expected by Windows would be worth a try.

              • Rick says:

                I figured out what was happening. I had some dummy tickers that I used to manually updated some accounts in Money. For some reason that is causing the error. I deleted the tickers and everything is working. Thanks for the help.
                Great program.

  5. Daniel says:

    This is a nice simple tool for updating yahoo stock quotes. There are two features, I’d like to see:

    1. Ability to delete a row or stock. I understand I can go in and edit the xml file .. but would prefer to just do it within the app. I’m an old guy and don’t want to risk making stupid mistakes while editing xml files.

    2. Ability to use yahoo to update the option prices automatically.

    • Bill says:

      Thanks for the comments… #1 you can already do, just click on the gray selection column on the left side of the row you want to delete and hit the Delete key.

      I’ll revisit the Options quotes when I get time… it doesn’t work the same as regular stock quotes and I’ll have to give it a round of fresh study.

  6. Russ says:

    Hi Bill,
    Any updates? I am now getting errors when I try either Google or Yahoo.
    Thanks!

    • Bill says:

      Russ,

      Yahoo seems to be working for me… see past notes in this thread about Google.

      If you download the latest copy of SunsetOFX, info on any failed quote downloads will appear after downloading quotes from Yahoo. If you run that and a particular symbol is making trouble I can try to see what is happening.

  7. W. Dogwood says:

    Starting last night, trying to obtain prices on Google failed with this error:
    ************** Exception Text **************
    System.Xml.XmlException: ‘doctype’ is an unexpected token. The expected token is ‘DOCTYPE’. Line 1, position 3.
    at System.Xml.XmlTextReaderImpl.Throw(Exception e)
    at System.Xml.XmlTextReaderImpl.Throw(String res, String[] args)
    at System.Xml.XmlTextReaderImpl.ThrowUnexpectedToken(String expectedToken1, String expectedToken2)
    at System.Xml.XmlTextReaderImpl.ParseDoctypeDecl()
    at System.Xml.XmlTextReaderImpl.ParseDocumentContent()
    at System.Xml.XmlTextReaderImpl.Read()
    at System.Xml.Linq.XDocument.Load(XmlReader reader, LoadOptions options)
    at System.Xml.Linq.XDocument.Load(String uri, LoadOptions options)
    at System.Xml.Linq.XDocument.Load(String uri)
    at WindowsFormsApplication1.SunsetOFXMainForm.UpdateQuotesGoogle()
    at WindowsFormsApplication1.SunsetOFXMainForm.UpdateQuotesbutton_Click(Object sender, EventArgs e)
    at System.Windows.Forms.Control.OnClick(EventArgs e)
    at System.Windows.Forms.Button.OnClick(EventArgs e)
    at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
    at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
    at System.Windows.Forms.Control.WndProc(Message& m)
    at System.Windows.Forms.ButtonBase.WndProc(Message& m)
    at System.Windows.Forms.Button.WndProc(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
    at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

    ************** Loaded Assemblies **************

    • Bill says:

      It looks like we’re the victim of another Google “retirement” of a service. My code in SunsetOFX used the iGoogle service to grab the quotes, and as of November 1 it was retired which is why you are seeing the error you are seeing.

      I’ll have to go back to the drawing board to re-do how I get quotes from Google, it looks like Yahoo is it for the time being.

  8. A Treutel says:

    THANK YOU SO MUCH for creating this tool! You are a lifesaver!! You should consider charging a nominal amount for this because your hard work is saving me TREMENDOUS time… I will definitely share your article!!!

  9. Sal says:

    Bill,
    Any way to offer an OFC format option in your program for users of Money 2004 and earlier? Your program works great for Money 2005 and later (OFX format). I’m guessing your program would need to convert the quote info from OFX to the OFC format before sending it to Money, if that is possible.
    Thanks

    • Bill says:

      The OFC file format itself is documented and it would be possible, but I’m hesitant to add that for a few reasons… Money Sunset is a free download (though you may have good reasons for not upgrading what you have), and there appear to be other ways to accomplish the same theing (convert the OFX to OFC). Also, I may not have a copy of Money that is that old which would complicate testing significantly.

      Here are two links I found for tools that claim to be able to convert between OFC, OFX, and other file formats. Theoretically you could run the output of SunsetOFX through one of these and get to an OFC file, MnyBank and MT2OFX.

  10. […] first (beta) app, which actually is not “new” in that it was released in 2011. It is SunsetOFX from Syntap Software and it is a simple Windows app that only requires .NET 3.5 which is native to Windows 7. If you are […]

    • Bill says:

      Thanks for the SunsetOFX review! One note, you don’t have to edit the app’s XML save file to remove a position, just click on the ticker row in the positions grid and hit the keyboard Delete button.

      • Bill says:

        Also, after some time has passed I am not positive the dummy account is needed. I create new positions in Money all the time and their prices update fine via SunsetOFX, but I haven’t bothered adding those to the dummy account for a long time. I have meant to research this a little further… I did make it to start the process but haven’t touched it since.

  11. Jackson says:

    Could you please make a win32 version? I’m still using XP, and it still works well for me. Thanks a million.

    • Bill says:

      Jackson,

      Somehow my last refresh of the beta included a version targeted for .NET Framework 4.5, which isn’t supported on Windows XP. I have replaced it with a .NET Framework 3.5 version. Though I don’t plan on re-writing the app for win32, SunsetOFX should now run fine for you assuming you have the proper .NET Framework installed. I ran it on a virtual XP machine and was able to create OFX files without problems. Good luck!

  12. Bill says:

    Super, glad you are finding it useful. I know where you are coming from with the same-day over-writes and acknowledge that is a weakness… I don’t know how to make those happen. What I usually do is pull down an update 6pm-ish EST and scroll down through the list to check the dates. If any haven’t updated for the day I either hold off and wait until a little later and repeat or just manually update those in Money.

    • Jeg says:

      Ok, thanks for the tip. Daily will be fine as long as I don’t do it in the morning 🙂

      • Bill says:

        I have run into situations where I’m trying to close out a month and I forgot to run SunsetOFX the night before (the 30th or 31st) and the updates would then show as that morning (the 1st). In those cases I change the system clock to “yesterday” and then apply the OFX file, then change the clock back and that seems to work.

  13. Jeg says:

    Hello,

    This works great! Thanks for sharing.

    Do you know how to get it to over-write updates on the same day? If I run it in the morning, then want to update prices in the evening, it won’t replace the prices from when I ran it in the morning.

    Thanks,

    Jeg.

  14. Bill says:

    Yes, once I get some time to version it to 1.0 I plan to post the source code under GPL as with the rest of my products.

  15. Ladislaus says:

    Would you be willing to share the .NET source code for your SunsetOFX tool?

Leave a Reply

Your email address will not be published. Required fields are marked *