Donate to Remove ads

Got a credit card? use our Credit Card & Finance Calculators

Thanks to MrFahrenheit,SalvorHardin,Anonymous,johnhemming,Anonymous, for Donating to support the site

Share Prices in Excel - NEW update

Discussions regarding financial software
kiloran
Lemon Quarter
Posts: 2580
Joined: November 4th, 2016, 9:24 am
Has thanked: 1177 times
Been thanked: 1244 times

Re: Share Prices in Excel - NEW update

#249568

Postby kiloran » September 5th, 2019, 1:22 pm

peterh wrote:
eventide wrote:It appears to me that the yahoo api is down (again). As usual my fear is they've switched it off permanently but in the past it has come back in a few hours or days.

In the event anyone still has the addin working it would be good to know as it would eliminate the above as a cause of my problems.

The tlfaddin is working for me at the moment - but a lot slower than usual. I thought that it might have been because I am on a different PC but, now I've seen this news, it could be related to the Yahoo issues.

I looked at the Excel and LibreOffice versions of HYPTUSS and they both successfully get prices from Yahoo, but very, very slow. Normally get data back in 1-2 seconds, but now it's nearer 30. Strangely, a browser-based query such as https://query1.finance.yahoo.com/v7/fin ... se.L,vod.l still works almost immediately, and this is the same data that is read by HYPTUSS

--kiloran

Breelander
Lemon Quarter
Posts: 3319
Joined: November 4th, 2016, 9:42 pm
Has thanked: 581 times
Been thanked: 1195 times

Re: Share Prices in Excel - NEW update

#249612

Postby Breelander » September 5th, 2019, 3:04 pm

kiloran wrote:I looked at the Excel and LibreOffice versions of HYPTUSS and they both successfully get prices from Yahoo, but very, very slow....


I have just tried in LibreOffice Version: 6.2.4.2 and the speed is normal. Yahoo has been having server problems this morning that took down Yahoo Mail (back now). This may have been a related problem.

kiloran
Lemon Quarter
Posts: 2580
Joined: November 4th, 2016, 9:24 am
Has thanked: 1177 times
Been thanked: 1244 times

Re: Share Prices in Excel - NEW update

#249622

Postby kiloran » September 5th, 2019, 3:32 pm

Breelander wrote:
kiloran wrote:I looked at the Excel and LibreOffice versions of HYPTUSS and they both successfully get prices from Yahoo, but very, very slow....


I have just tried in LibreOffice Version: 6.2.4.2 and the speed is normal. Yahoo has been having server problems this morning that took down Yahoo Mail (back now). This may have been a related problem.

Yes, looks like we are back to normal, Bree. I wonder if the Yahoo techies can now fix Brexit :)

--kiloran

eventide
Lemon Pip
Posts: 75
Joined: October 24th, 2017, 3:29 pm
Has thanked: 2 times
Been thanked: 69 times

Re: Share Prices in Excel - NEW update

#252579

Postby eventide » September 18th, 2019, 7:47 pm

I've replied to someone with an issue with this addin on another random request thread. I don't think it is an issue with the addin itself but you never know:

viewtopic.php?f=27&t=19575&p=252578#p252578

But as you can see from my response, basically I'm moving on from this addin solution to better technology so won't be able to support it much anymore on here. I hope it was useful while it lasted, and hopefully for a while more if the api continues to be supported by yahoo.

kiloran
Lemon Quarter
Posts: 2580
Joined: November 4th, 2016, 9:24 am
Has thanked: 1177 times
Been thanked: 1244 times

Re: Share Prices in Excel - NEW update

#252912

Postby kiloran » September 20th, 2019, 3:33 pm

eventide wrote:I've replied to someone with an issue with this addin on another random request thread. I don't think it is an issue with the addin itself but you never know:

viewtopic.php?f=27&t=19575&p=252578#p252578

But as you can see from my response, basically I'm moving on from this addin solution to better technology so won't be able to support it much anymore on here. I hope it was useful while it lasted, and hopefully for a while more if the api continues to be supported by yahoo.

Hi eventide. I was going to send you a PM but you have disabled the reception of PMs, so I hope you don't mind this public post.

You mentioned that you don't plan on further supporting the tlfAddin.xla. It's a tool which has clearly been well-received and it would be a pity if it were lost to the community. Would you have any objections if I added it to our software repository at http://lemonfoolfinancialsoftware.weebly.com/
I could add a few notes on its usage, and perhaps also provide some support in future (I've never played with Excel add-ins in the past, but I'm sure it's not grossly different to 'normal' VBA).
A user recently commented that the password 'ipisnotfree' did not work. I had the same problem, but managed to crack the password and remove it, so I could publish a password-free version in our software repository. I also quite fancy having a go at a LibreOffice equivalent, though that would have to wait for a while.

Any thoughts?

--kiloran

Itsallaguess
Lemon Half
Posts: 5369
Joined: November 4th, 2016, 1:16 pm
Has thanked: 2145 times
Been thanked: 3933 times

Re: Share Prices in Excel - NEW update

#253067

Postby Itsallaguess » September 21st, 2019, 4:49 pm

kiloran wrote:
A user recently commented that the password 'ipisnotfree' did not work.

I had the same problem, but managed to crack the password and remove it, so I could publish a password-free version in our software repository.


Are you sure you're using the XLA download linked from the first post on this thread kiloran?

https://www.lemonfool.co.uk/viewtopic.php?f=27&t=8332#p94736

I only ask because the download linked on that post does, for me at least, open the VBA using the 'ipisnotfree' password, so I just thought I'd check if there's a different version people are downloading from somewhere else that I might be missing?

http://www.mediafire.com/file/t5kdjwmhqg3njdf/tlfAddin.xla/file

Cheers,

Itsallaguess

kiloran
Lemon Quarter
Posts: 2580
Joined: November 4th, 2016, 9:24 am
Has thanked: 1177 times
Been thanked: 1244 times

Re: Share Prices in Excel - NEW update

#253102

Postby kiloran » September 22nd, 2019, 9:14 am

Itsallaguess wrote:
kiloran wrote:
A user recently commented that the password 'ipisnotfree' did not work.

I had the same problem, but managed to crack the password and remove it, so I could publish a password-free version in our software repository.


Are you sure you're using the XLA download linked from the first post on this thread kiloran?

https://www.lemonfool.co.uk/viewtopic.php?f=27&t=8332#p94736

I only ask because the download linked on that post does, for me at least, open the VBA using the 'ipisnotfree' password, so I just thought I'd check if there's a different version people are downloading from somewhere else that I might be missing?

http://www.mediafire.com/file/t5kdjwmhqg3njdf/tlfAddin.xla/file

Cheers,

Itsallaguess

You are right (now there's a surprise ;) )
There's no way of distinguishing one version from another, without having saved the files with different names.

Many thanks

--kiloran

eventide
Lemon Pip
Posts: 75
Joined: October 24th, 2017, 3:29 pm
Has thanked: 2 times
Been thanked: 69 times

Re: Share Prices in Excel - NEW update

#253387

Postby eventide » September 23rd, 2019, 6:39 pm

kiloran wrote:Would you have any objections if I added it to our software repository at http://lemonfoolfinancialsoftware.weebly.com/
I could add a few notes on its usage, and perhaps also provide some support in future (I've never played with Excel add-ins in the past, but I'm sure it's not grossly different to 'normal' VBA).
A user recently commented that the password 'ipisnotfree' did not work. I had the same problem, but managed to crack the password and remove it, so I could publish a password-free version in our software repository. I also quite fancy having a go at a LibreOffice equivalent, though that would have to wait for a while.

Any thoughts?

--kiloran


No problem

Kerryn
Posts: 1
Joined: March 1st, 2018, 11:37 am
Been thanked: 1 time

Re: Share Prices in Excel - NEW update

#277817

Postby Kerryn » January 16th, 2020, 11:12 am

I've been using this addin for a while now and find it very useful.
However recently it started throwing up ::tick_err:: for every reference.
Interestingly, when I installed an older version of Excel it sorted the problem - for a while, but then it re-occurred.
I was able to get at the code and the problem seemed to be that the XML call wasn't returning "OK" in .StatusText.
I've subsequently changed this to test for .Status = 200 and everything is running fine again.

Just thought I'd mention this in case anyone else is having a similar problem.

spiderbill
Lemon Slice
Posts: 415
Joined: November 4th, 2016, 9:12 am
Has thanked: 51 times
Been thanked: 72 times

Re: Share Prices in Excel - NEW update

#279621

Postby spiderbill » January 24th, 2020, 10:25 am

I've been using this for a while and it's been very good and reliable. (Thanks Eventide!) However just yesterday I noticed that it seemed to be missing changes from the previous close in a number of shares in my portfolio. Thinking it was likely to be a Yahoo problem I left it till later and by then things seemed to be better.
However today I'm again seeing problems - Marstons isn't showing the drop that others have reported after their trading update, and I see that National Grid isn't showing the increase from yesterday's 995.1p that is showing in HYPTUS and on Yahoo. Most of the others seem ok at the moment.

Is anyone else seeing similar problems? Anyone any clues?

I currently use Excel for my main financial spreadsheet recording individually bought holdings and other overall data, but use Libre Office for a heavily modified HYPTUS for my overall shares picture and dividends. Perhaps I may have to switch over to Libre Office for everything and link the prices from HYPTUS into the main sheet. But it was nice to have an alternative option to check prices from two different methods.

cheers
Spiderbill

spiderbill
Lemon Slice
Posts: 415
Joined: November 4th, 2016, 9:12 am
Has thanked: 51 times
Been thanked: 72 times

Re: Share Prices in Excel - NEW update

#279858

Postby spiderbill » January 25th, 2020, 10:47 am

An update on this.
15 minutes after my post yesterday I ran the extension again and this time got the right figures for MARS and NG, However later checks sometimes showed them sometimes to the original opening prices and sometimes being correct when compared to other sources.

After close of business they reverted again and I noticed that the main Yahoo listing was also wrong - showing MARS 117.90 in the listing despite showing 111.00 on the graph display.

This morning their listings were back to showing correctly at 111.00 but I ran the extension three times - first two times it remained showing 117.90 before changing to 111.00 on the third attempt. (NG also flipped over to showing correctly as 1014 instead of 995.10)

I conclude that the problem is at Yahoo's end but I'm wondering why the difference between this extension and the source that HYPTUS uses, since the latter seems to have been working ok.

Itsallaguess
Lemon Half
Posts: 5369
Joined: November 4th, 2016, 1:16 pm
Has thanked: 2145 times
Been thanked: 3933 times

Re: Share Prices in Excel - NEW update

#279863

Postby Itsallaguess » January 25th, 2020, 11:21 am

spiderbill wrote:
I conclude that the problem is at Yahoo's end but I'm wondering why the difference between this extension and the source that HYPTUS uses, since the latter seems to have been working ok.


Hi spiderbill,

The problem is at Yahoo's end (as is often the case with these price-anomalies...), but the Marstons issue you're seeing is randomly affecting both this price-extension and also the HYPTUSS tool.

You can see this if you update the HYPTUSS prices a few times, with the MARS price mostly coming in currently at 117.9p, but sometimes also coming in at 111p as well, although much less frequently.

In fact if you refresh the actual Yahoo Finance page for MARS itself, using the link below, then you'll also normally see the 117.9p price, but sometimes also then see the 111p price as we're sometimes seeing in both Excel tools -

https://uk.finance.yahoo.com/quote/MARS.L/

I suspect there's different servers feeding this single Yahoo data-point, and one of the servers is out of whack. We've seen similar anomalies in the past, and they usually go away after a day or two.

Hopefully the above at least explains that this is an issue affecting the Yahoo data itself, rather than one of the tools being discussed.

The current HYPTUSS tool uses exactly the same 'last-price' Yahoo scrape as the extension you're also using (thanks again to Eventide for making the improved pricing-routine publicly available...), so there's no difference in the default prices being returned (although the extension has other user-defined options for things like bid and ask prices, so the above is purely related to the default 'last price' data..).

Cheers,

Itsallaguess

spiderbill
Lemon Slice
Posts: 415
Joined: November 4th, 2016, 9:12 am
Has thanked: 51 times
Been thanked: 72 times

Re: Share Prices in Excel - NEW update

#279891

Postby spiderbill » January 25th, 2020, 1:51 pm

Hi Itsallaguess, many thanks for responding on this.

What you say is pretty much what I'd concluded, as since sending my earlier message I noticed that HYPTUSS price on National Grid was also reverting to yesterday's opening price when I ran an update. So both tools are being affected, but randomly so they don't always agree with each other. Dead helpful for debugging :lol:

It's useful to have it confirmed that both use the same source, which I hadn't been sure of. And I think you are probably right about there being two servers providing the data. Maybe one provides the listings price and one provides the graph price?!

The previous day the anomalies were affecting a much larger selection of my shares, around 15 or so, but yesterday and today it only appears to be these two, though of course there may be others which I don't follow. Let's hope they sort it out soon.

much obliged

Spiderbill

monabri
Lemon Quarter
Posts: 4221
Joined: January 7th, 2017, 9:56 am
Has thanked: 504 times
Been thanked: 1313 times

Re: Share Prices in Excel - NEW update

#279940

Postby monabri » January 25th, 2020, 5:43 pm

Following the link provided by IAAG, I noticed that Yahoo initially reported a share price of 111p at close on Friday 24th January. Refreshing the page several times (5 times?) reports 111p but then it reports the higher price and I note the date reported was now 22 January.

IanTHughes
Lemon Quarter
Posts: 1185
Joined: May 2nd, 2018, 12:01 pm
Has thanked: 583 times
Been thanked: 549 times

Re: Share Prices in Excel - NEW update

#279966

Postby IanTHughes » January 25th, 2020, 8:47 pm

spiderbill wrote:The previous day the anomalies were affecting a much larger selection of my shares, around 15 or so, but yesterday and today it only appears to be these two, though of course there may be others which I don't follow. Let's hope they sort it out soon.

I am not sure about previous days but, if anyone is interested, for last night's close the following also appeared to be incorrectly quoting the price for the 22 January:

Talktalk Telecom Group (TALK)
IG Group Holdings (IGG)
Compass Group PLC (CPG)


Ian

kiloran
Lemon Quarter
Posts: 2580
Joined: November 4th, 2016, 9:24 am
Has thanked: 1177 times
Been thanked: 1244 times

Re: Share Prices in Excel - NEW update

#279977

Postby kiloran » January 25th, 2020, 9:25 pm

spiderbill wrote:Hi Itsallaguess, many thanks for responding on this.

What you say is pretty much what I'd concluded, as since sending my earlier message I noticed that HYPTUSS price on National Grid was also reverting to yesterday's opening price when I ran an update. So both tools are being affected, but randomly so they don't always agree with each other. Dead helpful for debugging :lol:

It's useful to have it confirmed that both use the same source, which I hadn't been sure of. And I think you are probably right about there being two servers providing the data. Maybe one provides the listings price and one provides the graph price?!

The previous day the anomalies were affecting a much larger selection of my shares, around 15 or so, but yesterday and today it only appears to be these two, though of course there may be others which I don't follow. Let's hope they sort it out soon.

much obliged

Spiderbill

If you want to see the raw, unadulterated source of the data from Yahoo in a browser window, have a look at: http://lemonfoolfinancialsoftware.weebl ... nload.html
This is the data used by HYPTUSS and the Excel Add-in (and almost certainly any other spreadsheet-based tool.

The parameter for the live price is "regularMarketPrice", but other data such as "regularMarketPreviousClose" may be of interest to you.

You can just edit the URL for other Yahoo share symbols

--kiloran

IanTHughes
Lemon Quarter
Posts: 1185
Joined: May 2nd, 2018, 12:01 pm
Has thanked: 583 times
Been thanked: 549 times

Re: Share Prices in Excel - NEW update

#280001

Postby IanTHughes » January 26th, 2020, 12:33 am

It does seem that the Yahoo download is rather flaky at times. Here are the current incorrect prices shown on my download ...

Symbol | Current Price | Date       | Time      | Change     | Open   | High    | Low     | Volume 
CPG.L | 1912.5 | 22/01/2020 | 16:39 GMT | -24 | 1932.5 | 1945.5 | 1912.5 | 1874162
HMSO.L | 251.4 | 22/01/2020 | 16:35 GMT | -1.1000061 | 249 | 251.8 | 244 | 6513250
IGG.L | 683.6 | 22/01/2020 | 16:35 GMT | -6.800049 | 691.6 | 707 | 682.4 | 1101127
LAND.L | 957.6 | 22/01/2020 | 16:39 GMT | -8.400024 | 970 | 970 | 953.4 | 1630253
MARS.L | 117.9 | 22/01/2020 | 16:35 GMT | -1.0999985 | 121 | 121 | 118.191 | 1035435
NG.L | 995.1 | 22/01/2020 | 16:39 GMT | 7.799988 | 989.2 | 996 | 985.9 | 5626421
SIG.L | 308.2 | 22/01/2020 | 16:35 GMT | -3 | 310 | 312.6 | 306.7 | 1601085
SLA.L | 320 | 22/01/2020 | 16:35 GMT | 4.100006 | 317.4 | 320.9 | 315.2 | 4368392
TALK.L | 111.2 | 22/01/2020 | 16:38 GMT | -2.2000046 | 110.9 | 114.068 | 110.9 | 1117723




Ian

spiderbill
Lemon Slice
Posts: 415
Joined: November 4th, 2016, 9:12 am
Has thanked: 51 times
Been thanked: 72 times

Re: Share Prices in Excel - NEW update

#280046

Postby spiderbill » January 26th, 2020, 11:36 am

Thanks kiloran, that's interesting. I'll take a deeper look later today.

kiloran
Lemon Quarter
Posts: 2580
Joined: November 4th, 2016, 9:24 am
Has thanked: 1177 times
Been thanked: 1244 times

Re: Share Prices in Excel - NEW update

#280160

Postby kiloran » January 26th, 2020, 8:18 pm

kiloran wrote:
spiderbill wrote:Hi Itsallaguess, many thanks for responding on this.

What you say is pretty much what I'd concluded, as since sending my earlier message I noticed that HYPTUSS price on National Grid was also reverting to yesterday's opening price when I ran an update. So both tools are being affected, but randomly so they don't always agree with each other. Dead helpful for debugging :lol:

It's useful to have it confirmed that both use the same source, which I hadn't been sure of. And I think you are probably right about there being two servers providing the data. Maybe one provides the listings price and one provides the graph price?!

The previous day the anomalies were affecting a much larger selection of my shares, around 15 or so, but yesterday and today it only appears to be these two, though of course there may be others which I don't follow. Let's hope they sort it out soon.

much obliged

Spiderbill

If you want to see the raw, unadulterated source of the data from Yahoo in a browser window, have a look at: http://lemonfoolfinancialsoftware.weebl ... nload.html
This is the data used by HYPTUSS and the Excel Add-in (and almost certainly any other spreadsheet-based tool.

The parameter for the live price is "regularMarketPrice", but other data such as "regularMarketPreviousClose" may be of interest to you.

You can just edit the URL for other Yahoo share symbols

--kiloran

I forgot to add that you can beautify the JSON code to make it more human-friendly using a site such as https://extendsclass.com/json-validator.html
Load the URL or paste in the JSON code, then click Beautify

--kiloran

spiderbill
Lemon Slice
Posts: 415
Joined: November 4th, 2016, 9:12 am
Has thanked: 51 times
Been thanked: 72 times

Re: Share Prices in Excel - NEW update

#280168

Postby spiderbill » January 26th, 2020, 8:54 pm

Hi kiloran

Thanks for that, however Firefox seems to do an excellent job of formatting it without further intervention (unless one of my plug-ins is doing it for me without my realising it). Shows me the formatted JSON output, the Raw code (which seems to be what Chrome shows) and the Headers. It also has a filter which lets you zero in on particular items. Makes it much easier!

cheers
Spiderbill


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 4 guests