Remove ads

Introducing the LemonFools Personal Finance Calculators

Share Prices in Excel - NEW update

Discussions regarding financial software
funduffer
2 Lemon pips
Posts: 207
Joined: November 4th, 2016, 12:11 pm
Has thanked: 22 times
Been thanked: 63 times

Re: Share Prices in Excel - NEW update

#95651

Postby funduffer » November 14th, 2017, 4:37 pm

eventide,itsallaguess,

Many thanks for this.

I have never incorporated a share price scrape into any of my spreadsheets before due to my rather limited excel expertise.

However, I have now added in the add-in (so to speak) and it works a treat.

Excel 2013 running under Windows 10 in case you are interested.

FD

funduffer
2 Lemon pips
Posts: 207
Joined: November 4th, 2016, 12:11 pm
Has thanked: 22 times
Been thanked: 63 times

Re: Share Prices in Excel - NEW update

#95667

Postby funduffer » November 14th, 2017, 5:18 pm

funduffer wrote:eventide,itsallaguess,

Many thanks for this.

I have never incorporated a share price scrape into any of my spreadsheets before due to my rather limited excel expertise.

However, I have now added in the add-in (so to speak) and it works a treat.

Excel 2013 running under Windows 10 in case you are interested.

FD

I spoke a little too soon!

When I save, close and reload the sheet, the add-in is disabled. If I go to Manage add-ins and switch it off and then on again, it works again fine.

OK as a work around, but I think I may be missing something?

FD

GN100
Lemon Pip
Posts: 97
Joined: November 4th, 2016, 10:14 am
Has thanked: 7 times
Been thanked: 7 times

Re: Share Prices in Excel - NEW update

#95973

Postby GN100 » November 15th, 2017, 4:15 pm

I had some problems similar to yours - read through page 2 here and you may find an answer:-

viewtopic.php?f=27&t=8229&start=20

Sorry, I don't know how to link to the exact post as I used to on TMF.

eventide
Lemon Pip
Posts: 55
Joined: October 24th, 2017, 3:29 pm
Has thanked: 1 time
Been thanked: 52 times

Re: Share Prices in Excel - NEW update

#96015

Postby eventide » November 15th, 2017, 6:29 pm

GN100 wrote:I had some problems similar to yours - read through page 2 here and you may find an answer:-

viewtopic.php?f=27&t=8229&start=20

Sorry, I don't know how to link to the exact post as I used to on TMF.


Two thoughts:

(1) the addin rightclick menu additions only appear if the spreadsheet contains a getdata function call (no need for it otherwise!)


(2) Not sure if you have already done this, but you can avoid having to open the addin eachtime:

with excel open, search "addins", click the double cog icon, then browse, and browse to where you saved the addin, open it, and it should auto load from then on

it opens when excel opens

or in pre 2010 excel follow tools>addins >browse to location where you saved the addin>double click to "install the addin to run whenever excel is open

Nocton
Lemon Pip
Posts: 92
Joined: November 6th, 2016, 11:25 am
Has thanked: 9 times
Been thanked: 14 times

Re: Share Prices in Excel - NEW update

#98981

Postby Nocton » November 26th, 2017, 3:12 pm

Sorry to be behind on this as I have just seen this thread. I have Liveprice and, as reported, find it does not now work. I have downloaded tlfAddin.xla but not sure what to do with it. I have opened it. Nothing seems to happen and when I Refresh I get the error 'User-defined type not defined' on the line

Code: Select all

Dim MyHTTP As New XMLHTTP

I'm using an old version of Excel -2003 - but it works fine for other spreadsheets with macros.

Perhaps there is somewhere I can download a completely new copy of Liveprice with everything up to date, please?

Gostevie
Posts: 27
Joined: November 4th, 2016, 11:35 am
Has thanked: 32 times
Been thanked: 2 times

Re: Share Prices in Excel - NEW update

#117796

Postby Gostevie » February 13th, 2018, 8:37 am

Hi,

For the last couple of days, when I have been using this excellent add in, many of my shares are showing as nil value. It seems to affect those not listed in the FTSE350. Has anybody else noticed this?

Many thanks,

Steve

digitaria
Posts: 19
Joined: November 4th, 2016, 10:36 am
Has thanked: 23 times
Been thanked: 1 time

Re: Share Prices in Excel - NEW update

#117892

Postby digitaria » February 13th, 2018, 3:47 pm

Gostevie wrote:Hi,

For the last couple of days, when I have been using this excellent add in, many of my shares are showing as nil value. It seems to affect those not listed in the FTSE350. Has anybody else noticed this?

Many thanks,

Steve


I am seeing similar problems - I think they relate to the Yahoo feeds. Specifically, the Bid / Offer prices are nil for a lot of my shares and if my valuation is based on those, it is reduced accordingly. If you use the "Last" price, you get a better result. I hope Yahoo will correct the feed eventually.

kiloran
Lemon Quarter
Posts: 1085
Joined: November 4th, 2016, 9:24 am
Has thanked: 130 times
Been thanked: 407 times

Re: Share Prices in Excel - NEW update

#117899

Postby kiloran » February 13th, 2018, 3:58 pm

Gostevie wrote:Hi,

For the last couple of days, when I have been using this excellent add in, many of my shares are showing as nil value. It seems to affect those not listed in the FTSE350. Has anybody else noticed this?

Many thanks,

Steve

Could you give some examples of some shares that show nil value?

--kiloran

Gostevie
Posts: 27
Joined: November 4th, 2016, 11:35 am
Has thanked: 32 times
Been thanked: 2 times

Re: Share Prices in Excel - NEW update

#117919

Postby Gostevie » February 13th, 2018, 4:54 pm

kiloran wrote:
Gostevie wrote:Hi,

For the last couple of days, when I have been using this excellent add in, many of my shares are showing as nil value. It seems to affect those not listed in the FTSE350. Has anybody else noticed this?

Many thanks,

Steve

Could you give some examples of some shares that show nil value?

--kiloran


Many thanks for your reply.

BLVN.L and CNCT.L (Bowleven and Connect) are two.

kiloran
Lemon Quarter
Posts: 1085
Joined: November 4th, 2016, 9:24 am
Has thanked: 130 times
Been thanked: 407 times

Re: Share Prices in Excel - NEW update

#117926

Postby kiloran » February 13th, 2018, 5:14 pm

Gostevie wrote:
kiloran wrote:
Gostevie wrote:Hi,

For the last couple of days, when I have been using this excellent add in, many of my shares are showing as nil value. It seems to affect those not listed in the FTSE350. Has anybody else noticed this?

Many thanks,

Steve

Could you give some examples of some shares that show nil value?

--kiloran


Many thanks for your reply.

BLVN.L and CNCT.L (Bowleven and Connect) are two.


I tried =getdata("BLVN.L","last") and it gave the same data as the raw data from Yahoo: http://jsonviewer.stack.hu/#http://quer ... ols=blvn.L
(see the regularMarketPrice field)

(using Excel 2010 on Windows 7)

Also worked fine for CNCT.L

Maybe it's time-dependent, I'll check again at various times tomorrow.

--kiloran

eventide
Lemon Pip
Posts: 55
Joined: October 24th, 2017, 3:29 pm
Has thanked: 1 time
Been thanked: 52 times

Re: Share Prices in Excel - NEW update

#118018

Postby eventide » February 14th, 2018, 6:58 am

Gostevie wrote:Hi,

For the last couple of days, when I have been using this excellent add in, many of my shares are showing as nil value. It seems to affect those not listed in the FTSE350. Has anybody else noticed this?

Many thanks,

Steve



Yes, it is a yahoo feed issue not a problem with the add-in. Many securities are returning zeros for the bid and the offer, but last seems to be ok.

It may be a further deprecation of the yahoo server which would be unfortunate, but this has happened before and righted itself so it is fingers crossed.

Gostevie
Posts: 27
Joined: November 4th, 2016, 11:35 am
Has thanked: 32 times
Been thanked: 2 times

Re: Share Prices in Excel - NEW update

#118147

Postby Gostevie » February 14th, 2018, 1:11 pm

kiloran wrote:
Gostevie wrote:
kiloran wrote:Could you give some examples of some shares that show nil value?

--kiloran


Many thanks for your reply.

BLVN.L and CNCT.L (Bowleven and Connect) are two.


I tried =getdata("BLVN.L","last") and it gave the same data as the raw data from Yahoo: http://jsonviewer.stack.hu/#http://quer ... ols=blvn.L
(see the regularMarketPrice field)

(using Excel 2010 on Windows 7)

Also worked fine for CNCT.L

Maybe it's time-dependent, I'll check again at various times tomorrow.

--kiloran


Many thanks! I've replaced the "2" (bid) with "4" (last) in the formula and it now works. I'll try changing it back next week to see if the Yahoo issue has been solved, otherwise it's a decent fix. Thanks again for your help with this.

Steve

eventide
Lemon Pip
Posts: 55
Joined: October 24th, 2017, 3:29 pm
Has thanked: 1 time
Been thanked: 52 times

Re: Share Prices in Excel - NEW update

#119489

Postby eventide » February 21st, 2018, 2:05 pm

The absence of bid and ask field data appears persistent across all UK stocks but not for US or European stocks. I can't find any information as to why so still stuck using last data for the time being. This is generally ok for bigger stocks but you can get pretty stale data for less liquid stuff

Gostevie
Posts: 27
Joined: November 4th, 2016, 11:35 am
Has thanked: 32 times
Been thanked: 2 times

Re: Share Prices in Excel - NEW update

#119600

Postby Gostevie » February 22nd, 2018, 8:44 am

eventide wrote:The absence of bid and ask field data appears persistent across all UK stocks but not for US or European stocks. I can't find any information as to why so still stuck using last data for the time being. This is generally ok for bigger stocks but you can get pretty stale data for less liquid stuff


This now appears to have been fixed.

will89
Lemon Pip
Posts: 77
Joined: November 7th, 2016, 9:33 am
Has thanked: 2 times
Been thanked: 21 times

Re: Share Prices in Excel - NEW update

#122999

Postby will89 » March 7th, 2018, 9:13 pm

Eventide, my one true love.

I've been making use of this wonderful addin since launch, but I've come unstuck. I bought a Mac...

I've purchased Office 16 for Mac, but I can't get the addin to work. It's in the list of loaded addins but there is no extra functionality gained by right clicking, has anyone else had any luck?

Worryingly, I'm getting the following error at some points, which I presume is to do with the addin:
'Compile error in hidden module: 'sPrices'. This error commonly occurs when code is incompatible with the version, platform, or architecture of this application.'

Any help would be greatly appreciated.

eventide
Lemon Pip
Posts: 55
Joined: October 24th, 2017, 3:29 pm
Has thanked: 1 time
Been thanked: 52 times

Re: Share Prices in Excel - NEW update

#123131

Postby eventide » March 8th, 2018, 11:22 am

Won't work on Office for mac. Office for mac doesn't support VBA

a solution would be a VM (Parallels or something) with a full install of Windows and Office, although this defeats the point of having a mac

kiloran
Lemon Quarter
Posts: 1085
Joined: November 4th, 2016, 9:24 am
Has thanked: 130 times
Been thanked: 407 times

Re: Share Prices in Excel - NEW update

#123132

Postby kiloran » March 8th, 2018, 11:29 am

eventide wrote:Won't work on Office for mac. Office for mac doesn't support VBA

I thought that Office on a Mac supported VBA, but does not support ActiveX. I may well be wrong.

An alternative for the Mac might be http://lemonfoolfinancialsoftware.weebl ... crape.html
I'll probably release an enhanced version of the LibreOffice version in the next day or so.

--kiloran

eventide
Lemon Pip
Posts: 55
Joined: October 24th, 2017, 3:29 pm
Has thanked: 1 time
Been thanked: 52 times

Re: Share Prices in Excel - NEW update

#123134

Postby eventide » March 8th, 2018, 11:34 am

It may support a limited version of VBA or its own progamming language, but the MS libraries referenced in the VBA project will not be there unless it is a windows environment

will89
Lemon Pip
Posts: 77
Joined: November 7th, 2016, 9:33 am
Has thanked: 2 times
Been thanked: 21 times

Re: Share Prices in Excel - NEW update

#123143

Postby will89 » March 8th, 2018, 12:25 pm

kiloran wrote:
eventide wrote:Won't work on Office for mac. Office for mac doesn't support VBA

I thought that Office on a Mac supported VBA, but does not support ActiveX. I may well be wrong.

An alternative for the Mac might be http://lemonfoolfinancialsoftware.weebl ... crape.html
I'll probably release an enhanced version of the LibreOffice version in the next day or so.

--kiloran


Hi Kiloran,
Excel for Mac does have VBA, but I think you're right in suggesting that a basic macro might work better.
I've tried downloading the linked file, but I can't seem to make it work even on my Windows PC, do you need to copy and paste the code in VBA that's assigned to the sheet and then create a button in my own file that uses that code? I couldn't see a separate macro module in the file I downloaded. Basically, no idea how to get the functionality from your file into mine.
Apologies if I'm being stupid...

vrdiver
Lemon Quarter
Posts: 1092
Joined: November 5th, 2016, 2:22 am
Has thanked: 157 times
Been thanked: 321 times

Re: Share Prices in Excel - NEW update

#123182

Postby vrdiver » March 8th, 2018, 2:18 pm

This may be a "you're too dumb to use this" question, but here goes anyway :)

I've installed the tlfAddin.xla and when I put "=getdata("VOD.L","last")" in a call, which returns the Vod price, so seems to be working just fine.

Question: is there a user manual / url reference / instructions / idiot guide that I can go to to show me what other data is available and how to get it?

Also, (pretty please) are you giving the password to the vba out so I can take a look at the code?

VRD


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 1 guest