Got a credit card? use our Credit Card & Finance Calculators
Thanks to Anonymous,bruncher,niord,gvonge,Shelford, for Donating to support the site
Share Prices in Excel - NEW update
-
- 2 Lemon pips
- Posts: 102
- Joined: October 24th, 2017, 3:29 pm
- Has thanked: 3 times
- Been thanked: 83 times
Re: Share Prices in Excel - NEW update
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.
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.
-
- Lemon Quarter
- Posts: 4137
- Joined: November 4th, 2016, 9:24 am
- Has thanked: 3293 times
- Been thanked: 2871 times
Re: Share Prices in Excel - NEW update
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
-
- Lemon Half
- Posts: 9109
- Joined: November 4th, 2016, 1:16 pm
- Has thanked: 4140 times
- Been thanked: 10061 times
Re: Share Prices in Excel - NEW update
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
-
- Lemon Quarter
- Posts: 4137
- Joined: November 4th, 2016, 9:24 am
- Has thanked: 3293 times
- Been thanked: 2871 times
Re: Share Prices in Excel - NEW update
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
-
- 2 Lemon pips
- Posts: 102
- Joined: October 24th, 2017, 3:29 pm
- Has thanked: 3 times
- Been thanked: 83 times
Re: Share Prices in Excel - NEW update
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
Re: Share Prices in Excel - NEW update
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.
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.
-
- Lemon Slice
- Posts: 549
- Joined: November 4th, 2016, 9:12 am
- Has thanked: 159 times
- Been thanked: 184 times
Re: Share Prices in Excel - NEW update
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
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
-
- Lemon Slice
- Posts: 549
- Joined: November 4th, 2016, 9:12 am
- Has thanked: 159 times
- Been thanked: 184 times
Re: Share Prices in Excel - NEW update
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.
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.
-
- Lemon Half
- Posts: 9109
- Joined: November 4th, 2016, 1:16 pm
- Has thanked: 4140 times
- Been thanked: 10061 times
Re: Share Prices in Excel - NEW update
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
-
- Lemon Slice
- Posts: 549
- Joined: November 4th, 2016, 9:12 am
- Has thanked: 159 times
- Been thanked: 184 times
Re: Share Prices in Excel - NEW update
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
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
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
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
-
- Lemon Half
- Posts: 8511
- Joined: January 7th, 2017, 9:56 am
- Has thanked: 1569 times
- Been thanked: 3463 times
Re: Share Prices in Excel - NEW update
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.
-
- Lemon Quarter
- Posts: 1893
- Joined: May 2nd, 2018, 12:01 pm
- Has thanked: 731 times
- Been thanked: 1153 times
Re: Share Prices in Excel - NEW update
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
-
- Lemon Quarter
- Posts: 4137
- Joined: November 4th, 2016, 9:24 am
- Has thanked: 3293 times
- Been thanked: 2871 times
Re: Share Prices in Excel - NEW update
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
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
-
- Lemon Quarter
- Posts: 1893
- Joined: May 2nd, 2018, 12:01 pm
- Has thanked: 731 times
- Been thanked: 1153 times
Re: Share Prices in Excel - NEW update
It does seem that the Yahoo download is rather flaky at times. Here are the current incorrect prices shown on my download ...
Ian
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
-
- Lemon Slice
- Posts: 549
- Joined: November 4th, 2016, 9:12 am
- Has thanked: 159 times
- Been thanked: 184 times
Re: Share Prices in Excel - NEW update
Thanks kiloran, that's interesting. I'll take a deeper look later today.
-
- Lemon Quarter
- Posts: 4137
- Joined: November 4th, 2016, 9:24 am
- Has thanked: 3293 times
- Been thanked: 2871 times
Re: Share Prices in Excel - NEW update
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
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
-
- Lemon Slice
- Posts: 549
- Joined: November 4th, 2016, 9:12 am
- Has thanked: 159 times
- Been thanked: 184 times
Re: Share Prices in Excel - NEW update
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
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
-
- Lemon Quarter
- Posts: 4137
- Joined: November 4th, 2016, 9:24 am
- Has thanked: 3293 times
- Been thanked: 2871 times
Re: Share Prices in Excel - NEW update
spiderbill wrote: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
Well, how about that!
I use Chrome most of the time and I know there are Chrome extensions for formatting JSON, but I didn't know that Firefox handled JSON natively
Many thanks
--kiloran
-
- Lemon Slice
- Posts: 259
- Joined: November 5th, 2016, 12:02 am
- Has thanked: 283 times
- Been thanked: 72 times
Re: Share Prices in Excel - NEW update
Hi Itsallaguess,
been using the tlfAddin function for some time in a portfolio summary sheet, using option =getdata($A$1&".L","Last")
Instead of querying a stock price is it possible to retrieve the ftse100, ftse250 , etc index values?
I did try some options that excel objected to and options along the lines of
=getdata("^FTSE"&".L","Last")
but this returns a ticker error.
Can you or any other kind soul point me in the right direction?
Thanks
midgesgalore
been using the tlfAddin function for some time in a portfolio summary sheet, using option =getdata($A$1&".L","Last")
Itsallaguess wrote:...
...
If A1 contains this -
VOD.L
Then this is the formula you want -
=getdata($A$1,"Last")
If you want to just use London tickers (A1 = "VOD"), and don't want to append every ticker with ".L", then you could do something like this -
=getdata($A$1&".L","Last")
If anyone wants to use separate sheets for tickers and prices, then something like this will enable you do to so -
=getdata(TickerSheet!$C$13,"Last")
Cheers,
Itsallaguess
Instead of querying a stock price is it possible to retrieve the ftse100, ftse250 , etc index values?
I did try some options that excel objected to and options along the lines of
=getdata("^FTSE"&".L","Last")
but this returns a ticker error.
Can you or any other kind soul point me in the right direction?
Thanks
midgesgalore
-
- Lemon Half
- Posts: 9109
- Joined: November 4th, 2016, 1:16 pm
- Has thanked: 4140 times
- Been thanked: 10061 times
Re: Share Prices in Excel - NEW update
midgesgalore wrote:
Instead of querying a stock price is it possible to retrieve the ftse100, ftse250 , etc index values?
I did try some options that excel objected to and options along the lines of
=getdata("^FTSE"&".L","Last")
but this returns a ticker error.
You're nearly there, but you don't need to use the '.L' when retrieving index prices from Yahoo.
Here's a few UK index tickers that I've tried this morning, but so long as you can find the index on Yahoo, with a valid price and ticker in brackets after the index name, then you should be able to get anything available on the Yahoo Finance site -
https://i.imgur.com/CbFAgu1.png
Yahoo page for FTSE100, showing the relevant ticker to use in brackets - https://i.imgur.com/dg5dKVA.png
Hope this helps.
Cheers,
Itsallaguess
Return to “Financial Software - Discussion”
Who is online
Users browsing this forum: No registered users and 14 guests