Tracking CDR Investments

JoelC

I recently invested in a number of CDRs (i.e., USD stocks that are hedged and traded in CAD examples of which include APPL, HD [Home Depot], META, MSFT, V [Visa], etc.).

I want to track the daily performance of these holdings in the same way that I track the daily performance of my other securities. The problem is that I cannot figure out the symbol to use download the stock prices. The symbol(s) for the exchange that they trade on (i.e., CBOE Canada) do not download any historical prices.

For example as illustrated below, there are three CBOE symbols for the AAPLE CDR (i.e., APPL.DG, APPL.DY and AAPL.ZY) but none download any price history. The correct symbol (per Yahoo Finance and others) is APPL.NE which does not appear as an option.

Would appreciate an explanation about how to download the CDRs historical prices so my portfolio can be updated on a daily basis.

Thank you.

Tom Young

Well those clearly should work and in fact they do work when you use Quicken's Investing Center site:

https://www.quicken.com/investing/stock-quotes-investment-research/appl

but I couldn't get them to work in a Quicken test file.

Maybe try Official Quicken Support to see if they can come up with a better answer?

JoelC

@Tom Young appreciate the effort and good to know that it is not just me that could not get them to work. I can / will try Official Quicken Support but am not very optimisic.

Thank you.

denbe52

I wrote a relatively simple Python script (using yfinance) that automatically downloads the prices from Yahoo (e.g. AAPL.NE, AMZN.NE, etc) several times a day and at the end of day. The script creates a CSV file of the prices that I load into Quicken using ("File", "File Import", "Import Security Prices from CSV file") The CSV file looks like this:

AMZN.NE,22.65,07/05/2024
AMZN.NE,22.54,08/05/2024

You can download as much historical data as you need.

JoelC

@denbe52 appreciate teh update and great idea; would you mind sharing the Pythin script? Thank you.

denbe52

Sure - download the code from my GitHub

https://github.com/denbe52/CDR_prices_to_Quicken/blob/main/cdr.py

JoelC

@denbe52, much thanks, I will try it for both the CDRs as well as the few other securities that Quicken does not download.

Huge thank you!

denbe52

@JoelC - you need to install yfinance using the nospam option

pip install yfinance[nospam]

That will automatically install the requests and pyrate limiters

JoelC

@denbe52 appreciate the heds up. I will try to find to work on this over the weekend as I need to tale crash cours eon Python and go for there. I am looking forward to it, just hope the weather sucks.

JosefW

@JoelC @denbe52

Getting quotes from the NEO exchange (for these CDRs and many other ETFs) has been a major oversight on Quicken Canada's part for years. I am amazed at denbe52's ingenuity and resourcefulness, but Quicken should have fixed this years ago. Updating exchange quotes should be a foundational part of their program.

I just got an email from CEO Eric Dunn touting all the wonderful advances Quicken is making, but not a word about Canadians. - Link to message from Quicken CEO Eric Dunn

Canadians are such a small part of their user base that suggestions like these drop to the bottom of any user request list. The latest official note on this is that, with some 53 requests, it was upgraded to "Under Consideration" back in the fall of 2023 - "Under Consideration ·Last Updated October 2023 - Submitted for review on 8/28/23, CBT-167/CTP-7672" (No word since, but requests are still being posted.)

Here is a discussion underway on this -

https://community.quicken.com/discussion/7907149/get-quicken-canada-to-support-neo-exchange-6-merged-votes-edited

In the meantime, I just check the quotes from other sources, enter them manually and hope that someday they get around to adding it.

JoelC

@JosefW , I goe so frustrated that I ended up selling the securities, converting CAD to USD and buying the actual securities. I dug into the CDRs n greater details and was put off by the cost of the hedge.

@denbe52, I thank you for your solution noting I was able to get it working (with the help of a friend who is more Python capable than me).

Arctic Hare

https://community.quicken.com/discussion/comment/20434350#Comment_20434350

I've been expressing concerns regarding Quicken's newsletters for years. I've even complained to the Office of the President. They never contain any Canadian content. They very reinforce the message that Quicken views its Canadian users as 2nd or 3rd class.

JoelC

@Arctic Hare please, do not get me started.

I have four issues that Quicken determined were "must fix" issues because the math / reporting was wrong. I was working with their engineering team very closely then all of a sudden the person I was working with was no longer available, I was passed to the Office of the President and I now get e-mails that are identical (i.e., I think they are auto generated) and read that my issue is important, engineering is working on it but there is no end date in sight.

I am very frustrated with this particularly given that I discovered an anomaly that renders the P/L incorrect when an investment account is coded a a business account. What is the point of financial software when the P/L is wrong?

End of rant!

Arctic Hare

https://community.quicken.com/discussion/comment/20434428#Comment_20434428

I am receiving similar emails from the OotP. Mine are in relation to the inability to download transactions from BMO Bank of Montreal. The issue started at the beginning of February and there is no end in sight as to when it will be resolved. I will acknowledge that the emails I receive are not auto-generated, but fixing the inability to download transactions should not take months. I did have one major issue that took literally years to get resolved - with regular follow ups.

JoelC

@Arctic Hare , I am very sorry to read that. I hope both of our issues get resolved in the near term as they are critical functional and reporting issues. I feel and share your pain.

Brent H

Agree with the comments here. I received that same email from Quicken touting all of the exchanges that they now support, but am frustrated that I cant get the AMZN, MSFT and other symbols for CDR's from the NEO exchange. Congratulations to Denbe52 for the python code and import method to enable. Now if we can get quicken to see how simple it is, and import this functionality it would be great. But alas, the canadians here are such a small market, we get ignored.

denbe52

@JoelC @Brent H @JosefW

Downloading the CDR prices by running the python program that I developed is only the first step.

https://github.com/denbe52/CDR_prices_to_Quicken

To fully automate the process of importing CDR prices into Quicken, you need to do two additional things

Firstly, install a Free program called "System Scheduler" - see: https://www.splinterware.com/download/index.html

This program can automatically run the python CDR prices download program on a schedule - say once every 15 minutes or once an hour or once a day.

The second step is to install AutoHotkey - https://www.autohotkey.com/ .

AutoHotkey allows you to program (i.e. map) any key to a new action. For example, when I am using Quicken, if I press the F5 key, the CDR prices will automatically be imported into Quicken. That is equivalent to clicking: "File", "File Import", "Import Security Prices from CSV file…", and then entering the name of the csv file, and pressing "OK"

If I press F6, Quicken will automatically perform a One-Step Update (i.e. download all the other security prices from the Quicken server). If I press F7, both the CDR prices and the One-Step Update will be performed. These key mappings are only applicable when the Quicken window is in focus. They don't have any affect when you are using other programs.

So, if you set things up correctly there is no extra effort required to run the python program, and import the prices into Quicken.

The only downside is that you need to learn some new software. But AutoHotkey is one of the best programs that I have ever used - it has a myriad of applications - automatically importing prices into Quicken is trivial with AutoHotkey.

I have loaded the autohotkey script up to my github here: https://github.com/denbe52/Quicken-CDR-Prices-AutoHotkey-Script

You will not regret learning how to use AutoHotkey.

denbe52

@JoelC @JosefW @Brent H @Arctic Hare

The python script that I wrote to scrape CDR prices from Yahoo:

https://github.com/denbe52/CDR_prices_to_Quicken/blob/main/cdr.py

is the just first step in fully automating the import of CDR prices into Quicken.

There are a couple of enhancements that will complete the automation and provide a seamless method of loading the CDR prices.

Firstly, I use a program called System Scheduler to automate the execution of the python program - say once per hour or even just once a day after the markets close. It's an excellent free program - you can download it at;

https://www.splinterware.com/products/scheduler.html

Secondly, I use a simple AutoHotkey script to alter the function of some keys in Quicken. For example, when I press F5, AutoHotkey will replicate the key strokes required to import the CDR.csv file into Quicken. e.g. "File", "File Import", "Import security prices from csv file...", "automatically enter the full path and file name to the csv file", and then click "OK".

When I press F6, Quicken will perform a One-Step Update.

And when I press F7, AutoHotkey will send the commands necessary to both import the CDR prices, and to perform the One-Step Update - everything in one key stroke.

I mapped F5, F6, and F7, but you can pick any keys you want. The good thing is that these three key mappings are only active when Quicken is the active window - they have no effect in other programs.

I've uploaded the AutoHotkey script (quicken_import_cdr_prices.ahk) to my github:

https://github.com/denbe52/Quicken-CDR-Prices-AutoHotkey-Script/tree/main

AutoHotkey is an outstanding program, and it's well worth learning how to use it. It's indispensable!!
Once you learn AutoHotkey, you can use it to automate all sorts of tedious / repetitious tasks, expanding abbreviations, etc.

To summarize,

1. The python program scrapes the CDR prices from Yahoo.
2. System Scheduler automatically runs the python program every hour (or whatever interval you choose).
3. The AutoHotkey script maps the F5, F6, and F7 keys in Quicken to automatically load the CDR prices and perform the One-Step Update with one keystroke.

Arctic Hare

https://community.quicken.com/discussion/comment/20439817#Comment_20439817

This is a slick solution… but, more work (and learning) than I'm prepared to undertake for this requirement. It's been way too many years since I did any coding (to give you a clue, I'd want to code it in Turbo Pascal or C++)… I expect I could figure out the basics of the new tools with some effort, but I'm learning all too well that my appetite to learn exceed my capacity for non-sleeping hours!

JosefW

@denbe52

Thanks for digging into a work around. I don't have the knowledge or the patience to try this. What's really frustrating is that we're all paying handsomely for something that should be a top priority for Quicken in fulfilling a well advertised key feature.