Quicken Quotes Server on the Fritz?

Chris_QPW

I have uploaded the new version to my website, and added your comments about the differences to it.

DanaJ

I was looking at what Number’s STOCK returns, and right now (4:30pm eastern) it does return values for stocks, but it seems to be yesterday’s info. So not “live”. Is that what you see?

Edit: Funds return no values, just errors. For now...

Chris_QPW

DanaJ said:

I was looking at what Number’s STOCK returns, and right now (4:30pm eastern) it does return values for stocks, but it seems to be yesterday’s info. So not “live”. Is that what you see?

For what it is worth that is exactly what happens with Excel's functions.

fizzylogic

@DanaJ It will never show live. Rumor has it that in the early days of Numbers, the STOCK function would return real-time or slightly delayed quotes, but Apple/Yahoo pulled the plug on that capability a long time ago. Thus, for the most part, both functions work the same for stock prices.

The consolation prize appears to be that in addition to Closing, Open, Hi, Lo, and Volume, the STOCK function also offers around 20 additional stats, including Yield, 52-week Hi & Lo, 1-year target, and many more, but only for the most recent close date. (See the Numbers help file or type "=STOCK" into an empty cell while the "Format" sidebar is open to see all the details.)

The reason I made the change in the spreadsheet was because for closing mutual fund numbers, it was suggested that the STOCKH function might have a huge delay compared to STOCK. At least, that's what I'll be checking for tonight.

fizzylogic

> @DanaJ said:
> Edit: Funds return no values, just errors. For now...

@DanaJ What errors are you seeing, and where? (QIF Report sheet or Quote Data sheet).

I tried to ensure that missing or erroneous data would result in Quote Data cells displaying "NO DATA" or "ERROR" instead of the "red triangle of death," and the QIF Report would filter out (hide) any rows containing either of those text flags.

DanaJ

@fizzylogic I get “Data for the “high” attribute isn’t available for the selected symbol”. (I just used my previous formulas and changed STOCKH to STOCK and removed the date parameter. When the data is available, STOCKH returned the same value for close, high, and low.) As you said, we’ll have to watch what happens when the data becomes available later tonight.

fizzylogic

@DanaJ OK, it sounds like you're seeing this on your own spreadsheet, not the "fizzy" version I partially stole from you.

I made the change to use STOCK function only for the closing price, not for the high & low. I figured since those numbers should always be the same as one another for mutual fund NAVs, they would match up once the final numbers were posted for any given day. Thus, even if the "same-day" QIF import to Quicken didn't catch all the latest data, it would self-correct if subsequent imports also include enough trailing dates.

My use of STOCK instead of STOCKH when date = today is an attempt to address the issue raised last night that mutual funds were still showing the closing price from the previous day (historical) long after the closing prices for stocks had already posted for the current day.

Of course there's no guarantee this will work, or work consistently, but it seemed worth a try based on the observation that HACAX was still showing 100.84 on my spreadsheet after Yahoo Finance was already showing the actual close of 100.34.

DanaJ

Yes, I was using my original spreadsheet. I have updated it a bit, removing the individual columns B thru E, and incorporating their functions into the big formula generating the qif line. Plus I replaced IFS with IF for older Numbers versions as you suggested. I’ll get the new version to @Chris_QPW soon, but it’s pretty much the same concept.

Speaking of @Chris_QPW , I was thinking of another way to go, but it needs programming, probably Perl, and may be overkill. The idea is to scrape finance.yahoo.com pages for the data, maybe from each stock’s History page. I could see it being presented on a web page that stored each user’s desired symbols in a cookie. (I’ve generated web pages with scraping and cookies with Perl like that many years ago (15+), but it would take me a looong time to get back up to speed.) Maybe some young whippersnapper could get interested?

Chris_QPW

@DanaJ Sorry I'm not interested.  Why?

The same reason why I'm not a fan of Quicken Connect/Express Web Connect.  There isn't any standard, and they can change pages at any moment.  Anyone taking on such project just signed themselves up for lifetime maintenance nightmare.

Besides website code has changed a lot over the years.  It isn't just some HTML any more, there is serious code hidden in libraries that get called with different languages and such.

And BTW in the case of Quicken Connect/Express Web Connect, "scraping" is done as a last resort.  Usually Intuit can convince them to provide some kind of API.  Of course none of the APIs are standardized, and there are different one of different financial institutions, but on top of that from what I have seen they aren't even "stable" in the sense that they make changes to their website and it affects the data sent through the API.  Actually most likely the most unstable part of it is logging in.

DanaJ

I didn’t think you would be interested, based on earlier posts! 😀. That’s why we need more naive whippersnappers!

Chris_QPW

DanaJ said:

I didn’t think you would be interested, based on earlier posts! 😀. That’s why we need more naive whippersnappers!

  Yep!

BTW to "successfully" "read" web pages these days you need the guts of a web browser, and it has to be maintained because financial institutions and other sites don't like talking to out of date web browsers.  And given that the web browsers are update like weekly...

This also why even though it isn't as "slick" @dboltson's suggestion probably the most practical.  Humans can adjust when things on the site change, not to mention this method can probably be done on other sites too.  Here is that suggestion:
https://community.quicken.com/discussion/comment/20163242/#Comment_20163242  

fizzylogic

@DanaJ Wish I could help, but I haven't done any programming since college. I don't suppose there's much demand for Fortran or Pascal these days.

I've been meaning to look into the Google Sheets web app, which I don't believe uses Yahoo Finance for its quotes. Maybe I'll have some time after I finish our taxes.

Chris_QPW

fizzylogic said:

@DanaJ Wish I could help, but I haven't done any programming since college. I don't suppose there's much demand for Fortran or Pascal these days.

I've been meaning to look into the Google Sheets web app, which I don't believe uses Yahoo Finance for its quotes. Maybe I'll have some time after I finish our taxes.

Well I just tried Google Sheets and I can tell you that right now the most it will return is yesterday's mutual fund prices.  Actually just tried it with PTY (security) and it is giving yesterdays close too.

fizzylogic

Well this was a bust! My original spreadsheet (using STOCKH for today's quotes) updated today's closing prices between 8:30 and 8:45 pm EST. The new one using STOCK still hasn't updated. I should have added a simple test cell that does nothing but grab a closing price using STOCK, but at this late hour, I'm thinking there is probably an error in my formula.

Also:
- It looks like everything updated about 30 minutes later than last night. I don't know if it's due to end-of-week or if Yahoo Finance just opens the spigot at random times every night, like the TSP does.

- All of the mutuals I plugged in (HACAX, FSCRX, USVAX, PIMIX, CVTRX, and RYSEX) show closing prices that match the Yahoo Finance website (on my original spreadsheet).

- The spreadsheets running on Numbers 6.1/High Sierra definitely require quitting and relaunching the program for them to update. The ones on Numbers 10.3.9/Big Sur only had to recalculate to pull in tonight's quotes. (I thought it did so by itself the other night, but I must have hit "return" or done something to make it recalculate when I woke up the computer and switched from Safari to Numbers.)

I'll make the necessary tweaks to the spreadsheet over the weekend and test it for a night or two before uploading it again, probably on Wednesday.

DanaJ

Same for my test - STOCK has not updated. Also, strangely, iOS STOCKH has not updated. but MacOS has!

fizzylogic

I've got a feeling that with Numbers/Yahoo Finance, we'll just have to live with getting nightly quotes several hours later than from Quicken 2007's server.

On the other hand, we can now easily pull in more than one week's worth of quotes at a time.

fizzylogic

My spreadsheet problem turned out to have a simple fix, which I would have caught instantly if I had remembered to temporarily remove the "IFERROR" statement before changing a formula.

When I added an "IF" statement to check for today's date, I copy/pasted the STOCKH function and simply deleted the "H." The STOCK function, however, can only have a maximum of two arguments: symbol & price. Failing to delete the date argument resulted in a syntax error, but it didn't break the entire spreadsheet because the error only manifests when date = today. (Actually, if you only want the last closing price, STOCK just requires one argument--the symbol. Price is assumed unless you specify one of the other 24 choices.)

Since STOCK will always return the last closing price, regardless of date, an additional IF statement was added to prevent it from populating the closing price cells on weekends and market holidays. (This wouldn't show up on the QIF report, but looks strange in the Quote Data sheet tables.)

I think the spreadsheet is healthy now, but I going to observe after next couple of market closes and compare its performance with my original spreadsheet before uploading it again, probably on Wednesday afternoon.

fizzylogic

OK, my Numbers spreadsheet fix was indeed simple...and completely pointless! When Apple states the STOCK function "returns data from the previous market day’s close," they aren't kidding. They really mean the previous market *day* not the previous *close*.

Running my original spreadsheet side-by-side with the new version last night (Monday), the old one (using STOCKH) fetched closing numbers at around 9:30 PM. The new one, (using STOCK if date = today) just sat there. At midnight, however, it did update--populating the "today" cells with duplicates of Friday's closing numbers. @Chris_QPW observed this behavior with STOCK on March 11, but I didn't make the connection as to why it was happening until now.

Based on this, I reverted back to using only STOCKH, added a few cosmetic changes for readability, and uploaded the zip file of "Version 2.2" to Dropbox:

https://www.dropbox.com/s/j7mj0nsu2rnej7g/Quicken 7 Stock QIF-Maker V2.2.numbers 2.zip?dl=0

Usage:
- Open file in Apple Numbers (minimum version 6.1)
- Enter start and end dates in green blocks provided (maximum of 31 consecutive days)
- Replace sample ticker symbols in green blocks with your own (maximum of 24), ensuring they match the format used by Yahoo Finance at https://finance.yahoo.com
- Alternate symbols may be entered in the adjacent column if those used in your Quicken Portfolio are not recognized by Yahoo Finance
- Numbers 6.1 may require quitting and relaunching to fetch latest quotes from Yahoo Finance
- Numbers 10.3.9 may only require recalculating (e.g., change start or end date) or closing/reopening the spreadsheet without quitting the program.
- Copy & paste all "QIF Report" rows below the header to a text editor and save as a plain text file.
- If the file name ends with “.txt” change it to “.qif” or append .qif if there is no extension already.
- Be sure to include the first row containing “!Type:Prices” and the final row containing the caret (^) symbol.

- In Quicken 2007 for Mac, click on the File menu, then select “Import” and “From QIF…”
- Navigate to the newly created .qif file and click “OPEN.”

I'm considering this my "best and final" version unless someone discovers a problem and posts about it here (or I discover a problem myself). So far, Yahoo Finance has updated their closing numbers at a different time every night, anywhere from 8:15 to 9:30 PM. Getting numbers earlier from Quicken's server was nice, but the additional delay isn't really a problem for me.

For those who really need the data sooner, earlier posts on this thread by @dboltson describe an approach using live (or slightly delayed) quotes available from Morningstar and downloaded as a .csv file.

@Chris_QPW Please feel free to delete my previous attempt and replace with this version on your QuicknPerlWiz website.

Chris_QPW

@fizzylogic I have updated the website with your newest version.

fizzylogic

I just tried to edit my last comment and it disappeared. The same thing happened the other night and it showed up again after I had already typed it again from scratch.

After that experience, I now compose all my comments in a text editor first, then post. I'll wait awhile to see if my last comment reappears and if not, I'll repost it.

Chris_QPW

@fizzylogic this forum has some automatic "spam logic" that pretty much randomly submits comments to go through the moderators.  Once they approve them, they get posted.  You will see a "toast" message in the lower left when this happens.

fizzylogic

@Chris_QPW Ah, thanks for that! I couldn't for the life of me figure out what was going on. The edit I tried to make was to insert a carriage return before the dash in "-In Quicken 2007 for Mac" in the usage steps list.

It's correct in my text file, but must have gotten slightly garbled when I pasted into the comment window. As a result, the same carriage return is missing on your website where the usage notes are pasted in. Not a huge deal, but I can be a little OCD when it comes to text formatting.

Chris_QPW

@fizzylogic I fixed it.

kohster

Wow it's 9:38pm Central and the STOCKH function still isn't returning any data for today (3/16). Guess I'll have to import those prices tomorrow. Not worth staying up just to get the prices when there's a good book waiting to be read until I fall asleep.

fizzylogic

@kohster Ooh, you only missed it by 7 minutes! The update finally occurred around 9:45. I added a little table on the right side of my spreadsheet to log date & time. I plan to track the variance each night for awhile, and hopefully pin down the earliest/latest times we can expect the closing numbers to show up.

DanaJ

@fizzylogic I can confirm all the timings and STOCK / STOCKH differences you saw today (Mar 16). I hope to send @Chris_QPW a final(?) version of my spreadsheet tomorrow.

I’ve been thinking about how Q2007 used to do “Get Quotes” - it actually got the quotes from the trading days in the last week (usually 5 trading days), and you’d occasionally see lines for previous days mixed in with the current day when you looked at its download list. I’m guessing they might be corrections to those data? Anyway, I was thinking that might be a good thing to do every day (get the last 5)? Just a thought, as I assume that’d be easy to do with your version.

fizzylogic

@DanaJ said:
> you’d occasionally see lines for previous days mixed in with the current day when you looked at its download list. I’m guessing they might be corrections to those data?

I can't verify this, as I rarely looked at the download list, instead comparing my portfolio account totals to those on my brokerage, TSP, & DRiP account websites. I also reconcile the portfolios registers against my statements each month or quarter to make sure nothing is amiss.

The five day download limit has irked me for years--even more so over the past year. I'm a very infrequent trader and we haven't been spending as much or as often during the pandemic. I'm also retired, which can make it more difficult to keep track of days, especially now. As a result of all these factors, I would occasionally open up Quicken, only to realize I had missed couple of weeks worth of quote downloads.

I've mentioned elsewhere that it's my general practice to thin out my price lists a few times a year, deleting everything except quotes for the last trading day of the month, plus those corresponding to dividend payouts & transactions. This ensured I didn't hit any maximum quotes limits, but also lessened the pain of manually entering any prices I might be missing (unless I missed the end of the month, which I try never to do!)

This situation is what I had in mind when I composed my spreadsheet to permit a month's worth at a time. No more pressure to download quotes at least weekly! (We just have to stay up late if we want the current day's numbers.)

Chris_QPW

fizzylogic said:

The five day download limit has irked me for years--even more so over the past year.

That's interesting.  On the Windows side yes it downloads with a window of about a 5 days when ever you do a One Step Update, but there is also "historical prices" a person can select manually, with a bit of limitations.  If you select Month you get day prices. Year I believe it is weekly.  And then there is two years and five years, I believe those are monthly prices.

fizzylogic

@Chris_QPW Yeah, i seem to recall reading that the Windows version of Q7 offered a number of features that never made it into the Mac version. I'm not sure if the reverse is true.

Chris_QPW

fizzylogic said:

@Chris_QPW Yeah, i seem to recall reading that the Windows version of Q7 offered a number of features that never made it into the Mac version. I'm not sure if the reverse is true.

There was one feature for sure that didn’t make it from Quicken Mac to Quicken windows and that was the ability to look at past reconciles.  That feature just made it back into New Quicken Mac but still isn’t in Quicken windows.  And A new one is that both of them got the ability to close in account and on windows once you close it you can open it again on quick and Mac you can which is a real pain on the window side and why I don’t use close account at all.