Quicken Quotes Server on the Fritz?

fizzylogic

Greetings, Community! My attempt to download quotes yesterday in Quicken for Mac 2007 resulted in the Google search that brought me to this thread.

As a brief intro, I've been maintaining my finances on Apple machines since 1981 (Apple ][+ running "Personal Finance Manager"). Since he wrote "the only investment guide I'd ever need," I began using Andrew Tobias' "Managing Your Money" program when I got my first Mac. In 1994, I bought a Performa 637CD "Money Magazine Edition" (at Sears), which came with Quicken 4, MacInTax, and a bunch of other financial software, and I've been using Quicken ever since.

Like many of us, I'm currently on ver. 16.2.4. My daily workhorse is a 2017 27-inch Retina 5K iMac, which I'm keeping at High Sierra for now, although I have several other Macs running everything from Classic 8.6 to Big Sur. In anticipation of the day when I'm forced to move up from High Sierra (already there for this year's TurboTax), my Big Sur machine has Quicken 2007 running under Mojave on a VMware Fusion 12 virtual machine. It seems to work fine, except for this new inability to download quotes. Upon clicking the "Download Quotes" button, the text below the progress bar appears to strobe a few times between "Connecting to the Quicken Quotes server," and "Cancelling," before it gives up.

Many thanks to dboltson for starting this thread and detailing his successful workaround. Thanks also to koshster for sharing the link to Jason Strimpel's bulk stock downloader. HUGE thanks, however, to DanaJ for mentioning the "STOCKH" function in Numbers.

As an Excel user for several decades (Mac at home, Windows at work), I've never had occasion to even launch Numbers before today; I've always been able find an Excel function or program an expression for everything I needed in a spreadsheet. So, this afternoon I fired up Numbers ver 6.1 for the first time and successfully muddled through my first spreadsheet using this excellent YouTube tutorial from MacMost:

https://www.youtube.com/watch?v=K_RalbDlnLg
I was able to build a Numbers spreadsheet with each row showing: ticker, close, date, high, low, and volume (in that order to match Quicken) for one of my stocks over the month of February. Sure enough, everything matched perfectly with the "Security Detail" prices in my Quicken portfolio, so I'll consider it a successful proof-of-concept. I still need to climb the Numbers learning curve to automate the process of getting everything into the right format for a QIF file, so I plan to spend the rest of the afternoon watching some basic how-to videos.

My goal is to automate the entire process such that the only data I need to enter are my start & end dates for the import file. Unfortunately, I never learned scripting, so my process might not end up being the most elegant. Regardless, once I have an acceptable workflow, I'll post the details here, unless someone who actually knows how to do this stuff beats me to it...or unless Quicken turns the quotes download server back on for us!

DanaJ

I’ve just created a Numbers spreadsheet that generates the data for a QIF file into a single column. You can put in as many symbols as you follow, and get a file for each day. Then copy the column and paste it into a text file named, for example, “mar05.qif”, and import into Q2007. Beta testers? Is it possible to send a DM here?

DanaJ

https://www.dropbox.com/s/rtcnjdeh3v858qf/IMG_1902.jpg?dl=0

smayer97

@DanaJ Maybe connect with @Chris_QPW to have him post the file on his website for all to access?

Chris_QPW

@DanaJDanaJ said:

I’ve just created a Numbers spreadsheet that generates the data for a QIF file into a single column. You can put in as many symbols as you follow, and get a file for each day. Then copy the column and paste it into a text file named, for example, “mar05.qif”, and import into Q2007. Beta testers? Is it possible to send a DM here?

Don't think you can do direct messages on here until you have more "points/stars".
But is seems to me that if people want to do a "beta test" they can do exactly what you did with this JPG file.  Put out a share link using their favorite "cloud service".  That is how I do it for Quicken Windows patches on my website (OneDrive links).

DanaJ

@smayer97 Just sent him a note on his site!

Chris_QPW

DanaJ said:

@smayer97 Just sent him a note on his site!

Note, that QPW stands for "QuicknPerlWiz". 

DanaJ

Here’s a link to the Numbers file
https://www.dropbox.com/s/yvng6rha7vm5b6h/QIF Generator.numbers?dl=0

Just put the symbols you want into column A, and the date you want in cell A1. If you need more rows, just add them, making sure the formulas come along. Or delete rows if you need less. Then G2:G25 (in my case) can be copied and pasted into a plain text xxx.qif file for import.

One workaround I needed was that I was using different symbols for three market averages in Q2007, and used the symbols in column F if I needed a different one than Numbers does. If this doesn’t apply to you, just leave column F blank.

fizzylogic

DanaJ, that's a beautiful piece of work! You've saved me (and probably many others) hours of study time. I will need to to modify your expression in column G, though, since Numbers 6.1 does not support the "IFS" function. I do have it on my Big Sur machine (Numbers 10.3.9), but I'm hoping to run my spreadsheet on the same machine I use to run Quicken 2007 natively--an older Mac running High Sierra.

Also, a question: When I export my security prices to a QIF file, there is a caret (^) beneath every row, not just the last one. I assumed this was needed for the import file, as well. Not so?

Rodyeast

Count me in if someone steps up and helps with downloading quotes for Quicken 2007 Mac. Thanks!

dboltson

@fizzy: In a trial import, I just discovered today that a single caret (^) suffices, as an end of file marker.

Chris_QPW

I decided to delete this entry because I couldn’t get this forum to properly format the text without extra carriage returns and things. And in fact it doesn’t really matter since Quicken 2007 Mac isn’t going to change therefore if you can find a format that’s shorter that is all except then there is an a real harm in doing that.

DanaJ

@Chris_QPW You don’t need the Carat and !Type lines in between each quote. Just the one !Type at the beginning and one Carat at the end. Also, the quote lines can also have High, Low, and Volume, in that order, comma separated. (At least in Mac Q2007)

Chris_QPW

DanaJ said:

@Chris_QPW You don’t need the Carat and !Type lines in between each quote. Just the one !Type at the beginning and one Carat at the end. Also, the quote lines can also have High, Low, and Volume, in that order, comma separated.

I was stating what the official format is.  Quicken Mac is allowing “syntax errors”/“relaxed syntax checking”.  Given that Quicken Mac 2007 isn’t going to get any changes I see no harm in using what you have found, but if you were to extend what you are doing to other data like transactions it would probably trip you up.

DanaJ

All us Mac users are “relaxed”

dboltson

I have not Numbers on my 10.9.2 OS Mac, but thanks to the supportive exchange we're having here -- and with a big tip of the hat to DanaJ -- I've been inspired to streamline my approach, using Excel's Concatenate formula to eliminate much of the alchemical decanting I'd been doing up 'til now.

My first Aha was realizing that only ONE ^ is required for termination, as an end-of-file [EOF] marker following the data -- whose header remains !Type:Prices

The data section itself -- which, lacking Numbers, I continue to export as a *.cvs file from Morningstar, and open in Excel -- can be combined, for each ticker, into a single cell using the formula (here starting in lucky row number 13)

=CONCATENATE(B13,",",C13,",",D13)

. . . where column B holds the ticker, C holds the closing price, and D the date. The only really finesse here is filling column D with that date [not exported by Morningstar] and whose year format, I discovered through trial and error, must be two characters long [YY], not four. So I enter something like ="3/5/21" [with the quote marks] into the first ticker's column D, and then drag-copy all the way down. And then also drag down the above concatenation formula, which creates the preformatted all-in-one data file.

A single copy & paste from Excel (appending the header !Type:Prices, and the EOF caret marker) into my Text Editor suffices to create a functional Filename.QIF ready for import into Quicken.

A relative piece of cake, that obviates all my previous back-and-forth among Excel, Text Wrangler & MS Word!

I'm eager to give it a real world whirl tomorrow, following Monday's close (say, after 7 pm, when all the NAVs are in). It represents a significantly much more streamlined affair -- Best of luck, and thanks again to all!

Chris_QPW

Does anyone know if Excel on the Mac supports macros and like ActiveX?

Edit 

The reason why I ask is I decided to take a whack at this but I’m only on windows so I can’t check out was it would happen on a Mac.

Being a programmer I’ve course went right to writing code for this and I got something that works pretty good at least on the window side.

DanaJ

@Chris_QPW Looks like macros work:
https://support.microsoft.com/en-us/office/enable-or-disable-macros-in-office-for-mac-c2494c99-a637-4ce6-9b82-e02cbb85cb96
but it appears ActiveX was never imported to the Mac. I’ve never used either, but as I recall the recommendation has been to keep macros off for security reasons.

I can try running on Mac Excel, but I’ve not had a need to go past Office 2016.

DanaJ

Well, today’s Q2007 quotes didn’t show up in my Numbers spreadsheet around 7 pm Eastern, when Quicken Quotes would have had them. I didn’t check constantly, but just now after Midnight Eastern they’re here. We may have a “new normal” with Numbers/Yahoo.

dboltson

I've got a streamlined Excel file named "QIF Maker" to share; just tell me where.

The premise is that you download a cvs, exported from Morningstar; copy two columns from it over to QIF Maker [Ticker, and Close]; then a third column populates with ticker,close,date -- ready to copy into a text editor, and save for import into Quicken.

Very streamlined; works like a charm. (I've even got the date automatically set to "Today"). Make your own, if inspired, or let me know the best way to get a copy over to you!

kohster

I really like the Excel method because of the CONCATENATE function. However, I'm seeing an issue with the Excel stock data and Vanguard funds. The price parameter seems to give me the previous day's close, and the previous close parameter gives me the closing from 2 days ago. The Numbers data is consistent for all securities, and it looks like a Concatenate function is present there too, so I will likely be going with that application.

Chris_QPW

DanaJ said:

@Chris_QPW Looks like macros work:
https://support.microsoft.com/en-us/office/enable-or-disable-macros-in-office-for-mac-c2494c99-a637-4ce6-9b82-e02cbb85cb96
but it appears ActiveX was never imported to the Mac. I’ve never used either, but as I recall the recommendation has been to keep macros off for security reasons.

I can try running on Mac Excel, but I’ve not had a need to go past Office 2016.

Thanks.  Yes I know about the security concerns, but being able to use code gives you the most power.  So it will just be up to the user to decide.  I think I can avoid ActiveX by just not using any controls.  They make the interface look nicer, but are really necessarily the user can just enter values.

Note I'm thinking that this will be useful on the Windows side too for people using older versions of Quicken and/or needing download prices from exchanges that Quicken's quote service doesn't provide.

A long time ago I had created a program called ImportPrices for this very reason, but I dropped it after awhile when the Yahoo API I was using get discontinued.  That has been the history of these free APIs.  Hopefully with these coming in the big players tools they will be better maintained.  BTW ImportPrices like ImportQIF could set controls/press buttons in Quicken so it doe the full update automatically.  The one I'm playing with now takes to the point where it opens the QIF file, which at least on the Windows side is setup to open with Quicken, and then the user only has to select the option for the Security List and select OK.

Chris_QPW

DanaJ said:

Well, today’s Q2007 quotes didn’t show up in my Numbers spreadsheet around 7 pm Eastern, when Quicken Quotes would have had them. I didn’t check constantly, but just now after Midnight Eastern they’re here. We may have a “new normal” with Numbers/Yahoo.

kohster said:

I really like the Excel method because of the CONCATENATE function. However, I'm seeing an issue with the Excel stock data and Vanguard funds. The price parameter seems to give me the previous day's close, and the previous close parameter gives me the closing from 2 days ago. The Numbers data is consistent for all securities, and it looks like a Concatenate function is present there too, so I will likely be going with that application.

I haven't played with the Numbers (We do have an iPad it might work on), but with Excel there is a trade off that might apply to Numbers too.

There is two ways to get prices.  One involves using the Data -> Stock option to expand/convert the name/symbol and from there you can select to have different fields like the price after it.  This is the "real time" and has no history.

Then there is that STOCKHISTORY function, it will only get the closed price. As such it will certainly not get today's price until sometime after todays close.

Chris_QPW

DanaJ said:

I can try running on Mac Excel, but I’ve not had a need to go past Office 2016.

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?

Thanks.

Chris_QPW

dboltson said:

I've got a streamlined Excel file named "QIF Maker" to share; just tell me where.

The premise is that you download a cvs, exported from Morningstar; copy two columns from it over to QIF Maker [Ticker, and Close]; then a third column populates with ticker,close,date -- ready to copy into a text editor, and save for import into Quicken.

Very streamlined; works like a charm. (I've even got the date automatically set to "Today"). Make your own, if inspired, or let me know the best way to get a copy over to you!

Put a link in this thread.  That way others can try it right now, and I can get a hold of it.
Most likely later to day I will start on a web page or so for these.  @kohster that goes for yours too.  If you have any instructions for use, put them in too and I will put them on the webpage.  Also let me know if you want "credits/name" of some kind to be posted with it as an "identifier" since clearly we there is going to be more than one posted at the start and people will need to refer to them.

kohster

https://bt.ittns.northwestern.edu/julian/numbers-quotes/numbers-quotes.zip

kohster

that link will not be permanent, BTW

Chris_QPW

kohster said:

that link will not be permanent, BTW

That is fine.  Once I get the webpages up we can refer to them, and everyone following/referencing the thread should use that instead.

dboltson

Here's a ink to "QIF Maker.xls" at DropBox

https://www.dropbox.com/sh/m2kur513omcybwh/AACphKrQYoEThKsar2ZikeSPa?dl=0

Let me know if it works for you (the link, and the file) ~ Thanks!

Chris_QPW

OK Here is mine (on OneDrive):
https://1drv.ms/u/s!AkC-7jZCgVToiyiQDdn3Ztb4lR-A?e=CH6Jje

You will have to "Enable Contents" (enable macros) to use it.

Note that if you put in the "Days Back" that take priority over the Start and End Dates.
To use it put in as many symbols as you like and select Shift+Ctrl+G.  That will create a Data tab with the prices (Data tab gets recreated each time).  Shift+Ctrl+W will create the QIF file (it will ask where you want it).  For "questions" like Auto Import 0 is no, any other number like 1 is yes.

Hopefully these shortcuts are OK on a Mac.  If not they can be change by going to View -> Macros -> View Macros, select one for the Macros -> Options...

Note I had to use two Macros (Actual Subroutines) because Excel refuses to fetch the quotes until after it returns from the subroutine that puts in the formulas to get them.