Donate to Remove ads

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

Thanks to johnstevens77,Bhoddhisatva,scotia,Anonymous,Cornytiv34, for Donating to support the site

Share Prices in Excel - NEW update

Discussions regarding financial software
eventide
2 Lemon pips
Posts: 102
Joined: October 24th, 2017, 3:29 pm
Has thanked: 3 times
Been thanked: 83 times

Share Prices in Excel - NEW update

#94736

Postby eventide » November 10th, 2017, 3:05 pm

I've created a new thread as there's a new download link and it is now at the top:

http://www.mediafire.com/file/t5kdjwmhq ... fAddin.xla

Notes -
Solved: issue causing excel 2016 failure to open the addin
Added: menu functionality to auto-update prices (can be turned on and off)

For new users:
If you download it, you may have to unblock the file with file explorer
Addin needs to be open to work!
Retrieve a price by typing eg =getdata("VOD.L","last") into any cell on any of your worksheets
There are 20+ different fields
Right-click to refresh prices
Rightclick menu items only shown if you have made a getdata request

jkretsch
Posts: 3
Joined: November 8th, 2017, 3:34 pm

Re: Share Prices in Excel - NEW update

#94740

Postby jkretsch » November 10th, 2017, 3:14 pm

Thanks!! The new version of the add-in now works with my Excel 2013 running on Windows 10 Home 64-bit.

jkretsch
Posts: 3
Joined: November 8th, 2017, 3:34 pm

Re: Share Prices in Excel - NEW update

#94752

Postby jkretsch » November 10th, 2017, 3:37 pm

Well, I take that back. It doesn't work in Excel 2013. I'm able to add the add-in now without error. However, when I insert the getdata function into a cell it displays ?NAME for the cell value and I don't see the rt-clk options for tft.

eventide
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

#94755

Postby eventide » November 10th, 2017, 3:41 pm

It works fine in 2013, there's just some user error.
#NAME means the addin is not open, or is not in the same location as for when you first referenced it
I'm assuming you unblocked the addin using file explorer (file>properties)
I'm assuming you deleted the old download and aren't inadvertently referencing this (check with excel>data>edit links>)

hassettp
Posts: 11
Joined: December 18th, 2016, 11:02 am
Has thanked: 11 times

Re: Share Prices in Excel - NEW update

#94768

Postby hassettp » November 10th, 2017, 4:52 pm

Firstly, may thanks for producing the addin.

I’m having difficulty understanding the results. I am trying to download the share price and the dividend yield.

If I look at Vodafone on digitallook I see:
Price 218.15 Dividend Yield (Latest) 5.9% (Forecast) 6.0%

I have used the following formulas:
=getdata("VOD.L","last") gives me 218.1
=getdata("VOD.L","dy") gives 0.000775473400
=getdata("VOD.L","div") gives 0.172

So I’m getting the price correctly, but I can’t see how to get the yield.

I'm using Office 365 on Windows 10

Peter

simoan
Lemon Quarter
Posts: 2091
Joined: November 5th, 2016, 9:37 am
Has thanked: 463 times
Been thanked: 1456 times

Re: Share Prices in Excel - NEW update

#94778

Postby simoan » November 10th, 2017, 5:37 pm

hassettp wrote:I’m having difficulty understanding the results. I am trying to download the share price and the dividend yield.

If I look at Vodafone on digitallook I see:
Price 218.15 Dividend Yield (Latest) 5.9% (Forecast) 6.0%

I have used the following formulas:
=getdata("VOD.L","last") gives me 218.1
=getdata("VOD.L","dy") gives 0.000775473400
=getdata("VOD.L","div") gives 0.172

So I’m getting the price correctly, but I can’t see how to get the yield.

I'm using Office 365 on Windows 10

Peter

I think you are now asking questions about the Yahoo data rather than the add-in, which is not really fair on eventide. Interpreting the data is your problem :) You've picked a pretty strange example with VOD because it reports in Euros and the Yahoo data could easily be all over the place. I think it's great of eventide to make all the other fields Yahoo returns available using getdata() but it's down to each user to decide how to interpret and verify the accuracy of the data it returns. Just saying...

All the best, Si

eventide
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

#94786

Postby eventide » November 10th, 2017, 6:04 pm

simoan wrote:
hassettp wrote:I’m having difficulty understanding the results. I am trying to download the share price and the dividend yield.

If I look at Vodafone on digitallook I see:
Price 218.15 Dividend Yield (Latest) 5.9% (Forecast) 6.0%

I have used the following formulas:
=getdata("VOD.L","last") gives me 218.1
=getdata("VOD.L","dy") gives 0.000775473400
=getdata("VOD.L","div") gives 0.172

So I’m getting the price correctly, but I can’t see how to get the yield.

I'm using Office 365 on Windows 10

Peter

I think you are now asking questions about the Yahoo data rather than the add-in, which is not really fair on eventide. Interpreting the data is your problem :) You've picked a pretty strange example with VOD because it reports in Euros and the Yahoo data could easily be all over the place. I think it's great of eventide to make all the other fields Yahoo returns available using getdata() but it's down to each user to decide how to interpret and verify the accuracy of the data it returns. Just saying...

All the best, Si


Si is correct. In due course however I will be integrating upcoming divs, exdivdate and paydate as additional fields.

hassettp
Posts: 11
Joined: December 18th, 2016, 11:02 am
Has thanked: 11 times

Re: Share Prices in Excel - NEW update

#94879

Postby hassettp » November 11th, 2017, 11:11 am

I apologise if my posting is considered inappropriate. On 10Nov17 eventide wrote “I made a new thread and will try and support user queries on there (& not here anymore)

viewtopic.php?f=27&t=8332”

This was my first posting on LemonFool. I have been a member of TMF since its early days and had always found it a very friendly and helpful board. My posting was not addressed specifically to eventide, I was hoping that someone more knowledgeable than myself could answer my question.

You say that I have picked a strange example. I used the example given by eventide at the start of this thread. When I couldn’t understand the results, I used the example share BP which is the seed share in the Top-up spreadsheet. Unfortunately, both of these shares report on Euros.

I have tried again using LGen and get a price of 265.10 and a yield of 0.000553256 (=getdata("LGEN.L","dy")). I think that this is the trailing annual dividend rate, rather than the forward yield as Yahoo quotes a Forward annual dividend yield of 5.44% and Trailing annual dividend yield of 0.06%

As you say it is up to each user to decide how to interpret and verify the accuracy of the data it returns, which is precisely what I was attempting to achieve.

Once again, I would like to apologise if my posting is incorrect. I had thought that this thread was to support user queries relating to the excellent getdata addin. I stand corrected and I would appreciate guidance where I should post in future as I would like to know how to obtain the forward yield.

simoan
Lemon Quarter
Posts: 2091
Joined: November 5th, 2016, 9:37 am
Has thanked: 463 times
Been thanked: 1456 times

Re: Share Prices in Excel - NEW update

#94938

Postby simoan » November 11th, 2017, 2:19 pm

hassettp wrote:I apologise if my posting is considered inappropriate. On 10Nov17 eventide wrote “I made a new thread and will try and support user queries on there (& not here anymore)

viewtopic.php?f=27&t=8332”

Well, perhaps I need to apologise because I'm sorry if my reply seemed abrupt but there was a :-) in my post to indicate the manner in which it was intended. As you will know from TMF days, threads can go off-topic very quickly and I believe the idea behind eventide starting a new one here was to have the latest details at the top and deal with users having specific problems with the add-in compatibility with various versions of Excel, not to discuss the veracity of the data it returns over which he has no control.

Personally, I'm only using the price data as experience has told me not to trust Yahoo as a source of any other fundamental information on which to base anything else, as you have found. I am happy to enter data for dividends etc. manually from more reliable sources e.g. Thomson Reuters.

With regard to your LGEN example, at least it is clearer than VOD which has a share price in GBP and all other information in EUR. My data source which contains the latest broker estimates shows a dividend forecast of 15.3p for 2017 and 16.2p for 2018. The former would give a yield of 5.8% and the latter over 6% at a share price 265.10p, so the Yahoo numbers look wrong to me whichever way you look at it.

All the best, Si

Itsallaguess
Lemon Half
Posts: 9129
Joined: November 4th, 2016, 1:16 pm
Has thanked: 4140 times
Been thanked: 10023 times

Re: Share Prices in Excel - NEW update

#94939

Postby Itsallaguess » November 11th, 2017, 2:29 pm

simoan wrote:
With regard to your LGEN example, at least it is clearer than VOD which has a share price in GBP and all other information in EUR.

My data source which contains the latest broker estimates shows a dividend forecast of 15.3p for 2017 and 16.2p for 2018.

The former would give a yield of 5.8% and the latter over 6% at a share price 265.10p, so the Yahoo numbers look wrong to me whichever way you look at it.


To be fair, I don't suppose eventide can win here. Clearly there's lots of data-options available with this Yahoo feed, and he's trying his best to expose them, and has been asked to do so specifically for a number of these new options.

Having done so, I don't think it should then be eventide's responsibility to act as quality-controller on the data itself. I think that responsibility needs to come from anyone who's noticed anything un-towards, and it's useful to a much wider audience that this yield data is coming under some scrutiny.

I think we need to take a 'community-based' approach to raising these questions, and I think it was absolutely right that hassettp has done so, thus enabling some wider scrutiny to occur of the data itself, which seems to have been really usefully carried out in this yield-based example.

Cheers,

Itsallaguess

simoan
Lemon Quarter
Posts: 2091
Joined: November 5th, 2016, 9:37 am
Has thanked: 463 times
Been thanked: 1456 times

Re: Share Prices in Excel - NEW update

#94944

Postby simoan » November 11th, 2017, 2:46 pm

Itsallaguess wrote:To be fair, I don't suppose eventide can win here. Clearly there's lots of data-options available with this Yahoo feed, and he's trying his best to expose them, and has been asked to do so specifically for a number of these new options.

Having done so, I don't think it should then be eventide's responsibility to act as quality-controller on the data itself. I think that responsibility needs to come from anyone who's noticed anything un-towards, and it's useful to a much wider audience that this yield data is coming under some scrutiny.

I thought that's what I said!? I was just pointing out that this is not the right thread to start discussions about the Yahoo data... and here we are discussing the Yahoo data. Anything other than the share price I would not give the time of day to and I believe most experienced investors would do the same. Having misleading information is in many ways worse than having no information and time has told me Yahoo is not a good source of fundamental share data.

All the best, Si

eventide
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

#94951

Postby eventide » November 11th, 2017, 3:03 pm

I would not trust the fundamental data for UK stocks without checking what is coherent
I have no plans to build such a check into the addin. The addin is not responsible for the data itself, only delivering whatever is there

For US stocks it is generally fine hence I plan to leave the functionality in place



Out of interest, I use a module to deliver data on funds/oeics in a similar way, keyed off the ISIN. (NAV, navdate, size, ter, yld,1,3,5,10yr performance, category, benchmark etc). Any interest in that or is it all stocks, all the way, here?

Itsallaguess
Lemon Half
Posts: 9129
Joined: November 4th, 2016, 1:16 pm
Has thanked: 4140 times
Been thanked: 10023 times

Re: Share Prices in Excel - NEW update

#94961

Postby Itsallaguess » November 11th, 2017, 3:56 pm

Duplicate post - reported for deletion
Last edited by Itsallaguess on November 11th, 2017, 3:58 pm, edited 1 time in total.

Itsallaguess
Lemon Half
Posts: 9129
Joined: November 4th, 2016, 1:16 pm
Has thanked: 4140 times
Been thanked: 10023 times

Re: Share Prices in Excel - NEW update

#94963

Postby Itsallaguess » November 11th, 2017, 3:57 pm

simoan wrote:
Anything other than the share price I would not give the time of day to and I believe most experienced investors would do the same.

Having misleading information is in many ways worse than having no information and time has told me Yahoo is not a good source of fundamental share data.


Agreed Si, and I wasn't criticising in my earlier post, and really just joining in the discussion around the Yahoo data-quality. Apologies if it might have read that way, as it certainly wasn't my intention.

I agree that this thread isn't the best place to get into lengthy discussions in that area, but I think pointing out the potential issues might well be best-placed here briefly, just so people are aware of the potential risks of relying on the wider-available data without carrying out further due-diligence.

Cheers,

Itsallaguess

hassettp
Posts: 11
Joined: December 18th, 2016, 11:02 am
Has thanked: 11 times

Re: Share Prices in Excel - NEW update

#94973

Postby hassettp » November 11th, 2017, 4:18 pm

Thank you itsallaguess. I was trying to understand the data exposed by the getdata and explore any discrepancies.

My interest is that I have a spreadsheet with details of my investments and I have linked to HYPTUS to get the current share price and forecast yield.

One option would be for me to use getdata for share prices and HYPTUS for dividend forecasts (this still works). Alternatively I could move my spreadsheet over to Apple’s numbers which has an inbuilt function for price and forecast yield, but then I would lose pivot tables. Decisions, decisions.

I totally agree with eventide that getdata should not attempt to validate any data supplied by yahoo. It would be helpful to include in any documentation an explanation of the value returned e.g.
Case 15, "divyld", "dy", "dividendyield" [trailing annual dividend rate] but this could be produced by anyone with knowledge of the yahoo api.

I would use a function to get the price of funds as suggested by eventide if he is happy to provide this functionality.

Peter

GPhelan
Lemon Pip
Posts: 61
Joined: November 11th, 2016, 7:25 pm
Has thanked: 1 time
Been thanked: 27 times

Re: Share Prices in Excel - NEW update

#95034

Postby GPhelan » November 11th, 2017, 9:01 pm

Eventide asked
"
Out of interest, I use a module to deliver data on funds/oeics in a similar way, keyed off the ISIN. (NAV, navdate, size, ter, yld,1,3,5,10yr performance, category, benchmark etc). Any interest in that or is it all stocks, all the way, here?
"

Yes I have both shares and fund/oeics and have used a Liveprice style feed to provide data from Yahoo. HOWEVER, I found Yahoo to be a most UN-reliable source of such data. Firstly it was missing data for some major fund managers, such as Jupiter. Secondly some fund's data was frequently days or even weeks old. Thus whilst I continued to start with the Yahoo data I overwrote it with fund data sourced from a Morningstar Portfolio which usually reports current days values by 9pm. This however involved a manual data transfer.
THEREFORE if the alternative source of fund data you are now using is more complete than the old my answer is YES!

Itsallaguess
Lemon Half
Posts: 9129
Joined: November 4th, 2016, 1:16 pm
Has thanked: 4140 times
Been thanked: 10023 times

Re: Share Prices in Excel - NEW update

#95220

Postby Itsallaguess » November 13th, 2017, 5:42 am

hassettp wrote:
One option would be for me to use getdata for share prices and HYPTUS for dividend forecasts (this still works).

Alternatively I could move my spreadsheet over to Apple’s numbers which has an inbuilt function for price and forecast yield, but then I would lose pivot tables. Decisions, decisions.


I've sent you a PM regarding the HYPTUSS issue Peter. I'm keen not to clutter this thread with side-discussions if at all possible.

Cheers,

Itsallaguess

will89
2 Lemon pips
Posts: 110
Joined: November 7th, 2016, 9:33 am
Has thanked: 6 times
Been thanked: 37 times

Re: Share Prices in Excel - NEW update

#95250

Postby will89 » November 13th, 2017, 9:33 am

Idiot Excel question coming up:

I can't get it to read the contents of another cell to get the ticker I want to check the price for, I know this is just me being inept!
EG, in cell A1 i have 'VOD'. In cell A2 I want to show the price. I've tried stuff like =getdata("(A1).L","last") to no avail, what's the correct format?

Itsallaguess
Lemon Half
Posts: 9129
Joined: November 4th, 2016, 1:16 pm
Has thanked: 4140 times
Been thanked: 10023 times

Re: Share Prices in Excel - NEW update

#95252

Postby Itsallaguess » November 13th, 2017, 9:42 am

will89 wrote:
I can't get it to read the contents of another cell to get the ticker I want to check the price for, I know this is just me being inept!

EG, in cell A1 i have 'VOD'. In cell A2 I want to show the price. I've tried stuff like =getdata("(A1).L","last") to no avail, what's the correct format?


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

simoan
Lemon Quarter
Posts: 2091
Joined: November 5th, 2016, 9:37 am
Has thanked: 463 times
Been thanked: 1456 times

Re: Share Prices in Excel - NEW update

#95279

Postby simoan » November 13th, 2017, 11:02 am

eventide wrote:Out of interest, I use a module to deliver data on funds/oeics in a similar way, keyed off the ISIN. (NAV, navdate, size, ter, yld,1,3,5,10yr performance, category, benchmark etc). Any interest in that or is it all stocks, all the way, here?

I only hold one fund and I'm happy to update the price manually in Excel very occasionally but I'd still use this if you decide to make it available. I'm sure there are many others who hold more OEICS etc. alongside their individual shares who would find this extra functionality extremely useful.

All the best, Si


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 7 guests