Quicken Quotes Server on the Fritz?

dboltson

Just in case, I uploaded a ZIP file, too (smart idea, Chris!):

https://www.dropbox.com/s/8zkwdsmy7eua3bg/QIF Maker.xls.zip?dl=0

It's much less sophisticated, but all I really need [Ticker, Close, Date]

Just note that the data is not pulled into the spreadsheet automagically
(by Excel, or other)
but needs to be Copied from another source
(e.g., a cvs file exported from Morningstar)
and then Pasted in.

Chris_QPW

I have retrieved each of yours and have stored them on OneDrive, which is where I normally link to for files of this nature.  I'm going to list the share links below, and I will probably use the same share link on the webpage (unless of course there needed changes in the file as time goes on).

@kohster's "numbers-quote" Numbers
https://1drv.ms/u/s!AkC-7jZCgVToiyolBeiaAH5afrCz?e=4eaYh5

@dboltson's "QIF Maker" Excel
https://1drv.ms/u/s!AkC-7jZCgVToiykxvP9Kf_MscpQm?e=pXKkr4

Mine "Get Quotes" Excel, Note uses macros and needs STOCKHISTORY function
https://1drv.ms/u/s!AkC-7jZCgVToiyiQDdn3Ztb4lR-A?e=wxreEq

fizzylogic

I'd like to offer one more log to throw on the fire. My QIF generator is written in Numbers 6.1 and plagiarizes shamelessly from the file posted by DanaJ, using ampersands (&) to concatenate the terms for the output expression on each row. I kept the ability to enter alternate ticker symbols, but changed the "IFS" function to "IF" to allow it to work on older versions of Numbers. The date range can span from one to 31 consecutive days for up to 24 ticker symbols.

So far, it seems to be functional and robust. I tried it on several machines and OS versions, and was able open the spreadsheet on the version of Numbers installed on each machine to create a txt/qif using TextEditor. My test cases included my main workstation, a 2017 iMac running High Sierra, and a 2009 iMac running El Capitan, on which Quicken's quotes hadn't been updated in ages. Quicken had no problem importing a full month's worth of quotes at a time without choking. It also worked perfectly on Quicken 2007 running inside a Mojave virtual machine on a Big Sur MacBook Pro with Numbers 10.3.9.

This is my first attempt at using Numbers, so I'm sure the spreadsheet could be streamlined quite a bit. The first sheet features data entry and QIF-formatted output, but there are 24 tabs behind it (one for each ticker symbol) that automatically populate the results from Yahoo Finance. A full month's worth produces a report with over 700 rows, so it takes a few moments to calculate.

I've attempted to attach a screenshot, but it doesn't appear that I'm able to attach the file. I can see the "attach a file" button above the editing window, but everything is dimmed out except image files like PNGs and JPEGs. I don't know if it's because I haven't got enough points in the Community to upload the file, or if program files just aren't permitted. I'll try to upload it to Dropbox and post a link shortly.

Chris_QPW

@fizzylogic You can only attach images here.  If you use any of the "cloud services" like OneDrive, Dropbox, ... they are have a way to create a share link that you can post.  If not you can send me an email at support@QuicknPerlWiz.com (note I will problem remove this email address later just to cut down on spam).  Note that you can send email (but not an attachment) with the contact form at: http://www.quicknperlwiz.com/

fizzylogic

OK, I think I've successfully wrestled Dropbox into submission—I haven't used it since switching my 1Password from standalone license to subscription membership. It seems Dropbox has eliminated the ability to simply right-click on any file in the Public Folder to produce a link.

Hopefully, the following link will work for downloading and the spreadsheet will function as intended. Please let me know if there are problems in either of these areas.

https://www.dropbox.com/s/bcdzxpt5ngw5bcg/Quicken 7 Stock QIF-Maker.numbers?dl=0

kohster

Wow everyone thanks for sharing. I'd probably withdraw my Numbers submission given that fizzylogic's version is far superior to mine IMO (the most notable being able to do a range of dates, never mind the overall better look and feel).

Chris_QPW

kohster said:

Wow everyone thanks for sharing. I'd probably withdraw my Numbers submission given that fizzylogic's version is far superior to mine IMO (the most notable being able to do a range of dates, never mind the overall better look and feel).

I will post everyone's for now and then trim it down if there are "winners".

Note that I think it is basically essential to have a date range/pick up old prices.
There isn't anyone that is going to be able to be around on everyday to do the downloading so sooner or later they are going to have to get caught up on some prices.

Chris_QPW

Note the main reason I'm putting these in a ZIP file (if you didn't give me one) is that at least for the Excel ones, if you go to the OneDrive link it will open the spreadsheet in the web browser, which is probably not what is needed (they need to save it to their disk).

Chris_QPW

OK the direct link to the web page is:
http://www.quicknperlwiz.com/getprices.html

Using the site tabs: Quicken Info -> Get Prices

Note I used links to your profiles here, if you don't want that or need any other changes let me know.  I tried to capture "usage" from this thread, so that might to change, especially over time.

dboltson

Corrected link:

http://www.quicknperlwiz.com/get-prices.html

Chris_QPW

dboltson said:

Corrected link:

http://www.quicknperlwiz.com/get-prices.html

Oops.  I changed the menu from "GetPrices" to "Get Prices" and the software changed the link URL.   

DanaJ

> @Chris_QPW said:
> This might be a problem.  The getting of stock prices is very new in Excel.  It might not be in the 2016 version.  Can you go to the Data tab and see if it has a Stocks button?
>

I don’t see anything mentioning “Stocks” anywhere on the Data tab. (Mac Excel 16.16.27 (201012))

Chris_QPW

DanaJ said:

> @Chris_QPW said:
> This might be a problem.  The getting of stock prices is very new in Excel.  It might not be in the 2016 version.  Can you go to the Data tab and see if it has a Stocks button?
>

I don’t see anything mentioning “Stocks” anywhere on the Data tab. (Mac Excel 16.16.27 (201012))

Yeah I was afraid of that.  I will make a note on the website that it can only be used with Excel 2019/365.  Thanks for looking.

kohster

So far this week things have been going pretty well with testing all of the different importers. As I mentioned before, I think the one from fizzylogic (built off of DanaJ's work) is going to be my long term pick, assuming that the regular servers aren't coming back ever.

The biggest change I'll likely have to adjust to is just scheduling, which isn't a horrible thing. I've gotten used to checking prices around 5:30-5:45pm each market day, because at that point the Vanguard mutual fund prices had made it to whatever data source Intuit/Quicken was using. With the Numbers files pulling data from Yahoo Finance, the Vanguard data doesn't make it there until much later. I'm not sure exactly when, but it's late enough that I'm pretty much always going to be putting in yesterday's numbers. Again, this is not a horrible thing - one could make the argument that tracking prices on a daily basis is not the healthiest thing for someone who is not trading frequently. :)

DanaJ

Well, we’ve only had two days of the “New Normal” so far, but last night I checked around 11:30 pm Eastern, and the Numbers/Yahoo prices were ready. Maybe we need a hardy volunteer to narrow down the typical release time by spending an evening repeatedly reloading 😁

The NAVs on the Yahoo web site did appear around the same time as Quicken’s did (around 7 pm Eastern) and stocks and indices were shown and available on both all day long (15 minutes delayed). The Numbers/Yahoo API must be delaying them for some reason?

kohster

Mutual fund prices (not just Vanguard, but Fidelity too back when I had some of those) never got updated throughout the day for me via Quicken. Always had to wait until that close to 6pm Central window to get those prices.

fizzylogic

> @DanaJ said:
Maybe we need a hardy volunteer to narrow down the typical release time by spending an evening repeatedly reloading 😁

Hey, were you looking over my shoulder yesterday evening while I was repeatedly reloading?

Actually, I wasn't reloading Yahoo Finance, just forcing the spreadsheet to recalculate, but it wouldn't show anything newer than the previous day's quotes.

By around 8:30 p.m., it occurred to me that Yahoo Finance must have updated their quotes by now and sure enough, when I checked their website, the new data was posted. I found it was necessary to quit out of Numbers (not just close and reopen the spreadsheet) in order to pull in the new info. I'm repeating this exercise about every 30 minutes this evening to try and pin down the exact time.

Chris_QPW

fizzylogic said:

I found it was necessary to quit out of Numbers (not just close and reopen the spreadsheet) in order to pull in the new info. I'm repeating this exercise about every 30 minutes this evening to try and pin down the exact time.

Sounds like Apple decided to "optimize" Numbers so that "real time" traders wouldn't keep "bothering" their servers.   

As in it caches the prices it gets, and doesn't get them again unless you restart.

fizzylogic

For what it's worth, the Yahoo Finance site has had today's stock closing data up for at least 45 minutes (since 5:30 p.m.), but I still can't get them in Numbers as of 6:15. If I can get home in time, I'll try to start checking earlier tomorrow.

fizzylogic

(Should probably note that I'm in Eastern Standard Time)

Chris_QPW

For what it is worth I tried Excel and it doesn't have today's price yet either.

fizzylogic

At 8:00 p.m. EST, spreadsheet was still not updated. Reopened at 8:10 and it shows todays closing stats for everything except my one mutual fund (USVAX).

We'll have to monitor this for a while to see if it is consistent. I used to gripe that the nominal daily posting time for Thrift Savings Plan funds wasn't until 8:00 p.m. Often, though, the updates would post much sooner (or later), sometimes as early as 7:00 p.m. or as late as 9:00. No rhyme or reason. At least now, all my stuff seems to post at or near the same time. (Still waiting for USVAX at 8:20.)

fizzylogic

At 8:30, my USVAX is now updated. I don't know if NAVs for all mutuals post around the same time. I'll have to leave that as an exercise for someone else.

Chris_QPW

Well in Excel it just updated the close for the stocks.  Mutual funds haven't updated yet.

kohster

All my prices are populated (including Vanguard funds) as of 8:22pm Central.

Chris_QPW

Excel, just got VTI just now, but none others.

DanaJ

I've just downloaded prices, but for 4 of my stocks it shows yesterday's price, not today's, as given correctly on yahoo.com. oh oh! We may have to verify prices that show as unchanged. Or wait longer.

DanaJ

@fizzylogic What symbols do you use for the TSP funds? I haven't been able to find any that match what's on the TSP site.

Chris_QPW

DanaJ said:

@fizzylogic What symbols do you use for the TSP funds? I haven't been able to find any that match what's on the TSP site.

What is one of the symbols in Quicken?

DanaJ

HACAX Returned yesterday's 100.84, but actual is 100.34 . No error returned. Also PIMIX. CVTRX, and RYSEX.