Why is AAPL sorted to bottom of security symbol list when in ascending order? (Q Mac)

Bosco

When I sort by symbol in ascending order AAPL is the last one listed after XOM. It seems the sort function stops the sort after only considering the first character. Not a critical issue but annoying and seems like it would be a simple fix.

Ascending order securities.png

Descending order securities.png

Bosco

https://community.quicken.com/discussion/comment/20360492#Comment_20360492

Thanks to @Jon for finding the Danish reference to "aa". From that hint I looked at the Apple Language Advanced settings in Monterey 12.6.5 and changed List Sort Order from Norwegian Bokmål to Universal and it fixed the sort issue in Quicken. Thanks to all that contributed!

Apple original setting:

New setting:

Ps56k2

Wow - seems like the AAPL text might have something else hiding in front of the "A"

jacobs

For what it's worth, I see the same thing with AAPL in my securities list. I guess I never sorted by Symbol to see this before.

But… it seems it's been like this for a long time. I opened my old Quicken Mac 2017 and Quicken Mac 2015, and APPL is at the opposite end of the expected order in those as well.

I'd be interested to learn if anyone comes up with an explanation!

Jon

It's sorting in the correct order for me in the Securities window.

jacobs

Wow, that's weird. Here's AAPL showing up as the first security in descending order, where it should be the last (off the bottom, of this screenshot):

And here's another nugget of data. In this descending order list, note that "VAAPX" is also out of order; it should be at the end of the Vanguard funds, but it's sorting "VA" above "VW". The common element here is the "AA" letter combination in both AAPL and VAAPX.

So then I added two new securities starting with "Z", one of which contains "ZAA", and got the same result:

In descending order, we should see:
ZZLL
ZAAG
VWUSX
etc., ending with AAPL

Why is Quicken sorting a string of "AA" differently than we would expect for alphabetical order?

And why is @Jon getting a different result for AAPL sorting order?

Jon

I added VAAPX, ZAAG, and ZZLL to my securities list and they all appear in the correct positions:

I'm not doing anything special when I add these, just typing the symbol into the symbol field (though it didn't recognize VAAPX as a valid symbol).

Bosco

@Jon @jacobs The securities list does not sort properly for me, it sorts the same as the same as the portfolio view. There doesn't seem to be a special character in front of the two securities starting with AA. Definitely a problem with the invoked sort routine. Unless there is a secret setting for the sort function. 😉

Quicken Deluxe Version 7.0.3 (Build 700.48782.100)

macOS 12.6.5

jacobs

@Bosco You're seeing the same thing that I am, yet @Jon is seeing different behavior. Interesting mystery.

I wondered if it could have anything to do with how the data file originated (e.g. Quicken Mac 2007 or Quicken Windows), but I found another clue. I have one Mac which is still running macOS Mojave (hanging onto a few old 32-bit programs!), so on that Mac I have Quicken still running version 6.12.6, because it cannot run version 7.x. To my surprise, my data files on that Mac all show the securities sorting by Symbol in the expected alphabetic order. And the key here is that my test data files on this and my other Mac all come from the same live data file. It's not the version of Quicken that's a factor, because I have old versions of Quicken on both Macs, and it sorts securities correctly on the Mojave Mac and incorrectly on the Ventura Mac in all versions (Quicken 2015, Quicken 2017, Quicken 6.12.x/7.x).

So what's different? Well, obviously the operating system. I also have a Mac which runs Catalina; I tested on that, and the security symbols sort correctly. So it could be something in the OS in Ventura. Or, I'm now thinking this difference might be an issue with the actual hardware chip in the Mac. My Mac running Ventura is an M2 Mac; my other two are older Intel Macs. Is it possible that something in the software compiler used by Quicken is resulting in this strange sort order for securities with "AA" in the symbol?

And that thinking drove me to an additional test which I think confirms my thinking that there's something special about "AA" in the sort order on the M2/Ventura Mac: I looked at Payees and found that Payees containing "AA" sort out of order on the M2/Ventura Mac, but sort correctly on the Intel/Mojave and Intel/Catalina Macs.

This is from the M2/Ventura Mac, showing Payees sorted in descending alphabetical order. We'd expect to see names with Z first, ending with A at the bottom. But seven payees which contain "AA" sort out of order, before the Z's:

Note also that it's not just a capitalized "AA"; it's also true of lower case "aa" and mixed case "Aa". And as seen with the security symbols, the "AA" doesn't need to be the first characters; in the example above, to securities which start with the letter "Z" should start with "Zwilling" and end with "Zaady's", but "Zaa" sorts out of order before "Zw". Again, this does not happen on the Intel Macs running Mojave and Catalina.

But wait, there's more! I then decided to type in a similar group of names in Apple's Numbers application and sort them. Guess what? The "AA" names sort in the wrong order in Numbers, too!

So that rules out this being a problem with the Quicken or the software compiler Quicken uses. It's a Mac problem, tied to either the version of macOS or the hardware, or possibly the SQLite database built into macOS. Where do we go from here? I've asked a moderator to flag this thread for the developers, so perhaps someone from that team will post a reply. Otherwise, I think we have to accept for now that this is a sorting issue with macOS which would need to be resolved by Apple. I don't even begin to know how to report an issue like this with Apple; I imagine the Quicken folks, as registered Apple developers, might know how to do so.

P.S. @Jon Remind me what hardware and operating system you're running on, to see if it fits into the narrative I've spun here. I'm hoping you're going to tell me you're on an Intel Mac. 😉

Bosco

@jacobs thanks for the extended analysis. It is very interesting that Numbers doesn’t sort properly.

Jon

@jacobs Sorry. I've checked on both M1 and M2 Macs (both running latest Ventura 13.3.1(a)) and AAPL sorts correctly on both. I don't have an Intel Mac I can test with at the moment.

I did find something interesting, though. The fact that you saw this in both Quicken and Numbers (and I could not duplicate your results with Numbers either) makes me think that there's something going on at the OS level. So I went to System Settings and typed "sort" into the search bar and it directed me to the Language & Region tab. I didn't see any settings there that had anything to do with sorting, but when I clicked on the ? icon for help I found the following:

I have English (US) as my primary language, so that must not be one of the languages that lets you specify sort order. Those of you that are seeing this, do you have a different primary language selected and is there a List sort order setting on the Languages & Region tab in System Settings?

jacobs

@Jon I thought I was on to something with the differences between my Intel and M2 Macs, but you've confirmed you're getting different results on M1 and M2 Macs. So much for my theory! The puzzle goes deeper…

I have an M2 Mac running Ventura 13.3.1(a), and my Language & Region setting is "English (US) - Primary". I don't have a "Sort order" setting because it doesn't apply to English (US). So I think our setups are identical — yet we're seeing different sorting results, in both Quicken and Numbers. How's that possible? What's special about "AA"? I'm back to being stumped.

Jon

Here's a thread from 10 year ago where someone had this same problem in Pages, with double letters sorting after single letters - it isn't just "AA".

https://discussions.apple.com/thread/4007692

Edit: After looking at that again, it's referring to ordered lists, not strings in general, so maybe that's something different.

Jon

Here's a discussion where someone says that "aa" in Danish is equivalent to "å" and should be sorted after "z" (or at least that's how I'm reading it).

https://tex.stackexchange.com/questions/396126/sort-aa-as-an-%C3%A5-using-biber

Norwegian too, apparently:

https://en.wikipedia.org/wiki/Alphabetical_order#Language-specific_conventions

jacobs

So since we're all using US English, not Danish or Norwegian, why are we seeing this issue? And why are some of us seeing it, yet not seeing it with the same data file on an older Mac, and some of us are not seeing it at all? Hmmm…

Jon

Just out of curiosity, what do other repeated letters do for you @jacobs ? Do BBPL or CCPL sort correctly?

Jon

I forgot we did "ZZLL" already, that answers that question.

Bosco

I used the native Mac terminal mode to sort a simple text file. The output was correct. So why can't Quicken sort correctly?

File Security_list.txt before sort command issued in native Mac Unix.

BMY
ANGL
BLX
HNDL
IBM
LTC
MO
NGG
OKE
PSEC
QYLD
RYLD
XOM
AAPL

Output from sort command:

iMac:Desktop bruce$ sort Security_list.txt
AAPL
ANGL
BLX
BMY
HNDL
IBM
LTC
MO
NGG
OKE
PSEC
QYLD
RYLD
XOM
iMac:Desktop bruce$

Jon

The command line sort is a BSD unix utility, it's probably not using the sorting routines built into Mac OS X that Quicken & Numbers are using.

UKR

How does the Mac (and Quicken) store the Ticker Symbol internally in the data file? As single-byte ASCII code or double-byte Unicode? Or could there be a mish-mash of those, with some in ASCII code and some in Unicode, depending on how the symbols were entered or converted from?
Can somebody look inside the database, under the covers?

Just curious …

jacobs

Digging into the SQL tables is beyond my pay grade. 😉

I shared your thought at first, but it doesn’t seem to hold up when you consider (a) some of us see the out-of-order sort and others don’t, (b) with a test file spun off from my live data file, I see the out-of-order sort on one Mac and not on two others, and (c) the same out-of-order sort occurs in Apple’s Numbers application.

Bosco

https://community.quicken.com/discussion/comment/20360492#Comment_20360492

Thanks to @Jon for finding the Danish reference to "aa". From that hint I looked at the Apple Language Advanced settings in Monterey 12.6.5 and changed List Sort Order from Norwegian Bokmål to Universal and it fixed the sort issue in Quicken. Thanks to all that contributed!

Apple original setting:

New setting:

UKR

Thanks to @Jon for finding the Danish reference to "aa". From that hint I looked at the Apple Language Advanced settings in Monterey 12.6.5 and changed List Sort Order from Norwegian Bokmål to Universal and it fixed the sort issue in Quicken.

I just wonder … what possessed your Mac to change that List Sort Order setting from the Default?

Bosco

Probably an Apple OS update.

jacobs

Okay, so how does one accomplish this in Ventura? My Language & Region screen doesn't show a List Sort Order to check/adjust:

jacobs

Okay, I found a way to do it, although it's unintuitive. First, I had to add Norwegian Bokmål in the top Preferred Languages list. Doing so added List Sort Order to the bottom portion of the screen; it was set for Norwegian Bokmål. I changed in to Universal, quit and relaunched Quicken, and now Quicken securities sort as expected alphabetically. IO was able to delete Norwegian from the Preferred Languages list, leaving only English (US), and the behavior didn't change.

Conclusions:

• Since this happened to more than one of us, and since the odds of us both inadvertently doing something to add Norwegian as a language seems extremely remote, I think Apple did something in some update which changed the obscure List Sort Order setting for some users.
• If the sort order on your Mac is fishy, go to Preferences/System Settings > Language & Region.
• If "List Sort Order" is visible (macOS Monterey and earlier), change it to "Universal".
• If "List Sort Order" is not visible (macOS Ventura), add a language like Norwegian at the top and the List Sort Order will appear. Change it to "Universal". You can then delete the language you added at the top.
• Quit and relaunch Quicken (and other apps with sorting issues, like Numbers) to verify that sorting is now as expected in English.

Thanks to all for the head-scratching and sleuthing to get to the bottom of this odd little issue!

volvogirl

Wow, I don't have Mac but good sleuthing you guys!