USD and CAD in Same Account

scope11

New user here, past couple months. Loving this tool so far, but struggling to properly account for currency.
I am able to hold both USD and CAD (cash & equities) in my brokerage account. I can only set the account currency in PS to one or the other. I set it to CAD. If I understand correctly, I then need to add USD equity Buys with the price per share converted to CAD in the Transactions section.

When I do this, the following results in Holdings:
Original = Cost Basis shows as CAD, but Market Value shows as USD throwing the returns off.
CAD = Shows correct Cost Basis and Market Value, but seems to show a huge Exchange Rate impact
USD = Seems to show somewhat correctly, but then changes all the holdings in the account to USD

Further complicating this is having done Norbert's Gambit previously on a large sum at a certain FX rate. So the exchange rate on the date I bought won't match the actual exchange rate from when I exchanged the funds.

Someone simplify this for a dummy?
Thanks

VidasMatelis

PS allows to hold equities of both currencies in the same account, but cash could be held just in one currency. I would strongly suggest that you create 2 separate accounts with CAD and USD currencies and split your transactions that way.

emptyquarter

My account gives me no choice but to have USD ( the USD auto washes )and CAD together. I created two accounts and use the
trade confirmations for currency exchanges without specifying currency values. Rather I categorize selling value currency as a withdrawal and buying value currency as a deposit.

EDIT
I mean that I enter the dollar value of what I am selling as a withdrawal and the dollar value of what I am buying as a deposit. Thus moving the money from 1 account to the other.
END EDIT

I'm not sure if this is a true entry or not because exchange rate is not explicitly entered.

VidasMatelis

I believe 2 account approach should work and you can do any tricks to move money between accounts and do currency exchanges.

emptyquarter

Thank you Vidas,

I still cannot get quotes since the 17th. I posted a copy of my copy and paste of the new script in the Intraday quote thread. I`ve entering manually since it failed. The only thing I can think of is that I pasted incorrectly using notepad and/or that entering manual quote damaged the quote gathering process.

VidasMatelis

Intraday script just gets "temporary" quotes for the current day - they are not stored permanently. More info can be found here: http://www.portfolioslicer.com/scripts/quotes-data.html

So you need to confirm that you cannot get "historical" quotes - that is your Quotes\_Symbol_.txt file is not updated with previous day quotes. If that is the case, can you please post here log file from Scripts\Log\GetQuotes-Yahoo.txt - that log should tell you the reason why quotes are not found.

emptyquarter

20:51:38 --- Starting script GetQuotes-Yahoo.ps1
Symbol count: 19. MinDate: 2019-12-31
Symbol: ABX.TO. Next date: 2021-07-28. Quote file: C:\Users\canopus\OneDrive\PortfolioSlicer\Quotes\_ABX.TO_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/ABX.TO?period1=1627430400&period2=1627505504&interval=1d&events=history&crumb=z8oB00wPNUB
Done: ABX.TO. No new quotes found.

Symbol: D-UN.to. Next date: 2021-07-28. Quote file: C:\Users\canopus\OneDrive\PortfolioSlicer\Quotes\_D-UN.to_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/D-UN.to?period1=1627430400&period2=1627505504&interval=1d&events=history&crumb=z8oB00wPNUB
Done: D-UN.to. No new quotes found.

Symbol: NTR.TO. Next date: 2021-07-28. Quote file: C:\Users\canopus\OneDrive\PortfolioSlicer\Quotes\_NTR.TO_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/NTR.TO?period1=1627430400&period2=1627505504&interval=1d&events=history&crumb=z8oB00wPNUB
Done: NTR.TO. No new quotes found.

Symbol: ARLP. Next date: 2021-07-28. Quote file: C:\Users\canopus\OneDrive\PortfolioSlicer\Quotes\_ARLP_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/ARLP?period1=1627430400&period2=1627505504&interval=1d&events=history&crumb=z8oB00wPNUB
Done: ARLP. No new quotes found.

Symbol: BP. Next date: 2021-07-28. Quote file: C:\Users\canopus\OneDrive\PortfolioSlicer\Quotes\_BP_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/BP?period1=1627430400&period2=1627505504&interval=1d&events=history&crumb=z8oB00wPNUB
Done: BP. No new quotes found.

Symbol: BIDU. Next date: 2021-07-28. Quote file: C:\Users\canopus\OneDrive\PortfolioSlicer\Quotes\_BIDU_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/BIDU?period1=1627430400&period2=1627505504&interval=1d&events=history&crumb=z8oB00wPNUB
Done: BIDU. No new quotes found.

Symbol: BAYRY. Next date: 2021-07-28. Quote file: C:\Users\canopus\OneDrive\PortfolioSlicer\Quotes\_BAYRY_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/BAYRY?period1=1627430400&period2=1627505504&interval=1d&events=history&crumb=z8oB00wPNUB
Done: BAYRY. No new quotes found.

Symbol: IAU. Next date: 2021-07-28. Quote file: C:\Users\canopus\OneDrive\PortfolioSlicer\Quotes\_IAU_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/IAU?period1=1627430400&period2=1627505504&interval=1d&events=history&crumb=z8oB00wPNUB
Done: IAU. No new quotes found.

Symbol: TLT. Next date: 2021-07-28. Quote file: C:\Users\canopus\OneDrive\PortfolioSlicer\Quotes\_TLT_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/TLT?period1=1627430400&period2=1627505504&interval=1d&events=history&crumb=z8oB00wPNUB
Done: TLT. No new quotes found.

Symbol: GLD. Next date: 2021-07-28. Quote file: C:\Users\canopus\OneDrive\PortfolioSlicer\Quotes\_GLD_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/GLD?period1=1627430400&period2=1627505504&interval=1d&events=history&crumb=z8oB00wPNUB
Done: GLD. No new quotes found.

Symbol: SLV. Next date: 2021-07-28. Quote file: C:\Users\canopus\OneDrive\PortfolioSlicer\Quotes\_SLV_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/SLV?period1=1627430400&period2=1627505504&interval=1d&events=history&crumb=z8oB00wPNUB
Done: SLV. No new quotes found.

Symbol: VWOB. Next date: 2021-07-28. Quote file: C:\Users\canopus\OneDrive\PortfolioSlicer\Quotes\_VWOB_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/VWOB?period1=1627430400&period2=1627505504&interval=1d&events=history&crumb=z8oB00wPNUB
Done: VWOB. No new quotes found.

Symbol: BNS.TO. Next date: 2021-07-28. Quote file: C:\Users\canopus\OneDrive\PortfolioSlicer\Quotes\_BNS.TO_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/BNS.TO?period1=1627430400&period2=1627505504&interval=1d&events=history&crumb=z8oB00wPNUB
Done: BNS.TO. No new quotes found.

Symbol: VTIP. Next date: 2021-07-28. Quote file: C:\Users\canopus\OneDrive\PortfolioSlicer\Quotes\_VTIP_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/VTIP?period1=1627430400&period2=1627505504&interval=1d&events=history&crumb=z8oB00wPNUB
Done: VTIP. No new quotes found.

Symbol: HIVE.V. Next date: 2021-07-28. Quote file: C:\Users\canopus\OneDrive\PortfolioSlicer\Quotes\_HIVE.V_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/HIVE.V?period1=1627430400&period2=1627505504&interval=1d&events=history&crumb=z8oB00wPNUB
Done: HIVE.V. No new quotes found.

Symbol: GLDM. Next date: 2021-07-28. Quote file: C:\Users\canopus\OneDrive\PortfolioSlicer\Quotes\_GLDM_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/GLDM?period1=1627430400&period2=1627505504&interval=1d&events=history&crumb=z8oB00wPNUB
Done: GLDM. No new quotes found.

Symbol: HMUS.NE. Next date: 2021-07-28. Quote file: C:\Users\canopus\OneDrive\PortfolioSlicer\Quotes\_HMUS.NE_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/HMUS.NE?period1=1627430400&period2=1627505504&interval=1d&events=history&crumb=z8oB00wPNUB
Done: HMUS.NE. No new quotes found.

Symbol: FTFT. Next date: 2021-07-28. Quote file: C:\Users\canopus\OneDrive\PortfolioSlicer\Quotes\_FTFT_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/FTFT?period1=1627430400&period2=1627505504&interval=1d&events=history&crumb=z8oB00wPNUB
Done: FTFT. No new quotes found.

Symbol: BABA. Next date: 2021-07-28. Quote file: C:\Users\canopus\OneDrive\PortfolioSlicer\Quotes\_BABA_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/BABA?period1=1627430400&period2=1627505504&interval=1d&events=history&crumb=z8oB00wPNUB
Done: BABA. No new quotes found.

20:51:46 --- Finished. Quotes Requested/Succeed/Failed/Rows: 19/19/0/0. Duration: 8 sec.

VidasMatelis

Can you please click on this first requesting URL, and see if you can open file with a quote, or are you getting any error:

Requesting url: https://query1.finance.yahoo.com/v7/finance/download/ABX.TO?period1=1627430400&period2=1627505504&interval=1d&events=history&crumb=z8oB00wPNUB

emptyquarter

I fixed it. I found that my CAD account was showing +100% in holding and did some deposit adjustments to bring it back to 100%.

Now the quotes work.

I think I corrupted the Portfolio slicer spreadsheet at around the same time the Intraday problem started and assumed that was the issue.

I got todays ABX.TO quote from the request URL.

Thanks for you help Vidas.