First use - Yahoo Intranet quotes returns 'Not Found (web err)'

MikeP

Hi Vidas,
Greetings from the UK.

Thank you so much for creating this very interesting Excel based Investment Portfolio Slicer v3.10 - it is a Masterclass and I decided on test it out with a view to making it my defacto tracker.

I followed the instructions on your YT channel and read your online step by step guide on how to setup the spreadsheet. I spent some time on the DEMO spreadsheet to see how you have populated all the tabs. I entered my test data into the Empty spreadsheet and ran the batch file.

As below the scripts returned no errors. But noted the below underlined Yahoo Intranet quotes returned 5 fails with Not Found (web err)

I tried manually to query the below url in bold and didn't have any issues accesssing the Yahoo Page, but for some reason the script query seems to be failing to connect.

Could do with your assistance on the above issue,

Thanks!
M

17:14:26 Script: Yahoo Intraday . Quotes Requested/Succeed/Failed/Rows: 5/0/5/0. Duration: 5 sec.

I delved into the "QuotesIntraDay" folder and found it was empty and proceeded to checked the Log files. And I found the below errors in the GetQuotes-YahooIntraday.txt .

17:14:26 --- Starting script GetQuotes-YahooIntraday.ps1
Symbol count: 5. MinDate: 2021-12-31
Symbol: 0P0000KSP6.L. Next date: 2024-06-15. Quote file: C:\PortfolioSlicer\PSv3.1\Empty\PSDataExtract\Quotes\_0P0000KSP6.L_.txt
  Requesting url: https://finance.yahoo.com/quote/0P0000KSP6.L
  0P0000KSP6.L - Not Found (web err) 

Symbol:       BT-A.L. Next date: 2024-06-18. Quote file: C:\PortfolioSlicer\PSv3.1\Empty\PSDataExtract\Quotes\_BT-A.L_.txt
  Requesting url: https://finance.yahoo.com/quote/BT-A.L
  BT-A.L - Not Found (web err) 

Symbol:       VHVG.L. Next date: 2024-06-18. Quote file: C:\PortfolioSlicer\PSv3.1\Empty\PSDataExtract\Quotes\_VHVG.L_.txt
  Requesting url: https://finance.yahoo.com/quote/VHVG.L
  VHVG.L - Not Found (web err) 

Symbol:        VOD.L. Next date: 2024-06-18. Quote file: C:\PortfolioSlicer\PSv3.1\Empty\PSDataExtract\Quotes\_VOD.L_.txt
  Requesting url: https://finance.yahoo.com/quote/VOD.L
  VOD.L - Not Found (web err) 

Symbol:       WEAT.L. Next date: 2024-06-18. Quote file: C:\PortfolioSlicer\PSv3.1\Empty\PSDataExtract\Quotes\_WEAT.L_.txt
  Requesting url: https://finance.yahoo.com/quote/WEAT.L
  WEAT.L - Not Found (web err) 

17:14:31 --- Finished. Quotes Requested/Succeed/Failed/Rows: 5/0/5/0. Duration: 5 sec.

VidasMatelis

I am assuming that your historic quote script works OK (you have quotes in "Quotes" folder and just "QuotesIntraDay" folder is empty.
First symbol 0P0000KSP6.L has "Next Date: 2024-06-15". If you go to that symbol historical quote page you will see that last available quote is 2024-06-14

So simply Yahoo still does not have up to date quotes in historic tab. Usually this gets resolved within few days by itself.
Other 4 symbols have "Next date: 2024-06-18" - that is today's date. "Not found (web err)" indicates that intraday quote is not available yet. Again, might be just a timing issue - might get available in a few hours.
And when you will get "full" quote for 2024-06-18, then Next date will be "2024-06-19" and it will not be available till 2024-06-19 late evening or maybe next day.
So sometimes quotes might be a bit late.

MikeP

Thank you so much for your speedy response.

The Quotes folder did download the prices - so all working good.

Yes, you are right about the time lag for Yahoo to update the closing prices of Funds which usually can take 48 hrs for the settlement price. However, Yahoo always lags behind by 3-4 days. But that's fine we can leave with that.

While testing the reporting, I uncovered another issue and I believe this should be common to anyone who hold any UK traded holdings. Yahoo quotes prices for most of the UK traded Stocks and Funds in (0.01)GBp format, i.e in penny's. The ETF prices are in GBP so no issues there.

You will be able to see the calculation errors that I am seeing in the reporting. The Fund and Stock prices are multiplied by 100 and therefore give a wrong valuation.

Is there a method I can use to divide the calcuations by 100 to arrive at the accurate GBP valuation?

VidasMatelis

In psConfig.txt file in section you can specify Factor for Intraday or Historical quote. Factor 0.01 would divide your quote by 100.

Yahoo: list of symbols from Yahoo Finance website. Format: Symbol,MinDate,MaxDate,IntraDayFlag[Y|N],[DividendFlag[Y|N],FactorHistory,FactorIntraDay,FactorDividend

Here is example:
ABC.L,2019-01-01,,Y,N,,0.01
EFG.L,2019-01-01,,Y,N,0.01,0.01

So first line will apply factor 0.01 just for intraday quotes and second symbol EFG.L will apply 0.01 factor for both historical and intraday quotes.

You will need delete file with existing quote and re-run extracts after specifying this factor in psConfig.txt

MikeP

I made the above changes for the symbols that are priced in pence and deleted the files in the Quotes folder and ran the update and it resolved the issue - Thank you for pointing what I needed to modify in the psConfig.txt.

In my setup I only have GBP but there is one ETF WEAT.L which is priced in USD. When this ETF was purchased, I added the brokerage commisions for the currency conversion and calculated the unit price to be £34.26 but the price lookup that Yahoo downloads is in USD as $23.86.

So I added USD as a second currency but that did not make any difference. Is there a method to convert the USD price of WEAT.L and display it in GBP in the report?

VidasMatelis

So it is critial here to :
1. Setup correct currency for your account
2. Setup correct currency for your symbol.
3. If there is more than one currency either for account or symbol you need to add secondary currency to "ReportCurrency" table and setup scripts to download exchange rates.
4. When you enter data into transactions table, it is assumed that any dollar amounts are entered in the currency of the Account. If you account currency is GBP, but symbol is USD, then you can populate ExchRate column that that will convert PRice and Fee columns by applying ExchRate.
5. In the reports you will have to select currency that you want to use for reporting - do not forget this step!

So you can choose to create new account with USD currency and add WEAT.L symbol (make sure it is marked as USD) transactions in that account. Or, if you are using single GBP account, you will enter Price and Fee is USD and will also specify ExchRate to convert this to your Account GBP currency.

MikeP

Thank you for your reply.

To keep things simple, I would prefer to do what you suggested, i.e "Or, if you are using single GBP account, you will enter Price and Fee is USD and will also specify ExchRate to convert this to your Account GBP currency."

I did the above, but then I don't have the ability to get a valuation in GBP in the report.

I think I will have to opt for creating a new account with USD as the default currency. But if did that will the report display the valuation in GBP?

Last but not least for some reason the Intraday script continues to return Not Found (web err) and the folder is empty. With this failing does it have any effect in terms of reporting?

VidasMatelis

Do see GBP in report - you still need to:

1. Setup correct currency for your symbol.
2. If there is more than one currency either for account or symbol you need to add secondary currency to "ReportCurrency" table and setup scripts to download exchange rates.
3. In the reports you will have to select currency that you want to use for reporting - do not forget this step!

MikeP

Thank you for the quick note. I did as you had mentioned previously, in my GBP account, I used the statement cost price including the currency fees etc and converted it back to USD and I did what you mentioned above and I think I have figured it out. Thank you!

All working well - just the IntraDay quotes continues to fail.

MikeP

Downloaded the new Powershell script from this link and the Intraday downloads are now working.

MikeP

I noticed an issue pertaining to a Vanguard Fund symbol 0P0000KSP6.L, for some reason it does not display the
profit %. Whereas for say ETF's and Stocks it does and I am wondering if this is could be due either because Fund prices on Yahoo lag on average by two days. Or have I missed something in the psConfig.txt file.

Below is what I have set for the above symbol:

MikeP

While testing, I decided to do some modeling of say purchasing £100,000 worth of the Vanguard S&P500 ETF VUAG.L during the market low of Mar 2020 to analyse how this ETF has performed and Annualized RoR since then. Below is the the example. Btw I used the NAV price of £35.9925 from Vanguard on 20 March 2020.



For 2020 the start amount of £100,000 is not populated in the above table and the growth is calculated to be 37,440 for 365 days, giving a RoR of 37.4%, but in the report it shows the figure to be 50.07%.

Could do with your input on how the 50.07% figure is calculated.

VidasMatelis

Annualized rate is calculated based on principals similar to those described here: https://www.investopedia.com/terms/a/annualized-rate.asp
To this report you can add measure Profit% and that would probably be the value you are expecting.

MikeP

Thank you for your reply.

It looks like got confused with the formulae that I found here, probably not the same as Annulized RoR.

MikeP

I have repeatedly been having a problem with entering new Buy/Sell transactions. The new row will not accept the symbol entry despite the fact that it is in the database and has also been previously entered in the same account. Below is the issue, the total amount calculations stop working although at the same time but when I look a the cell they do have a lookup formula.

I close and open the spreadsheet a few times and often it fixes the issue by itself.



Any ideas of what could be causing this and or how to avoid it?

VidasMatelis

IN Transactions worksheet go to very right and place your cursor in Symbol column last row. Then copy formula from the formula bar and post it here. Also check if formula is showing any errors in it.