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
innocuous
Posts: 27
Joined: September 19th, 2020, 6:34 pm
Has thanked: 8 times
Been thanked: 3 times

Re: Share Prices in Excel - NEW update

#419701

Postby innocuous » June 15th, 2021, 3:01 pm

innocuous wrote:
Itsallaguess wrote:I've just downloaded a fresh copy of the XLA add-in from here -

http://lemonfoolfinancialsoftware.weebly.com/share-prices-in-excel.html

I've opened a new Excel workbook (Windows 10 / Office 2007), with the following VBA references set -

  • Visual Basic For Applications
  • Microsoft Excel 12.0 Object Library
  • OLE Automation
  • Microsoft Office 12.0 Object Library

I've then activated the downloaded add-in via Excel Options / Add-ins / Manage Excel Add-ins, and made sure that the .xla file is added to the add-in references and activated.

I've then entered 'VOD.L' into Cell E7, and in Cell G7, I've typed the formula '=getdata(E7)', and pressed return.

At first, Cell G7 contained the text '::queued::', but when I right-clicked the cell, I got the option to 'Refresh prices once', and when I selected that option, Cell G7 then refreshed to display the correct Vodafone price of 131.28p

Cheers,

Itsallaguess


Thanks for checking - I am going to try it again totally fresh. I will also try on another PC and see if that works or not. Back shortly with the results. :-)


This is weird. So just used my standard work laptop win 10 build with Office 365 pro. Downloaded addin from your link; Activated addin in Excel; Shutdown Excel; Open new book and test VOD.L - ::tick_err:: then GOOG - :tick_Err::

I then edited the VB in fUtilities to make .StatusText <> 200 alteration. Test again. still get ::tick_err::

So on one PC where I do have admin ability and could have done stupide stuff to break underlying code bases required to make the addin work it is not functioning; but even on my corporate machine that is totally locked down; I also cant get it to work.

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

#419705

Postby Itsallaguess » June 15th, 2021, 3:14 pm

innocuous wrote:
This is weird. So just used my standard work laptop win 10 build with Office 365 pro. Downloaded addin from your link; Activated addin in Excel; Shutdown Excel; Open new book and test VOD.L - ::tick_err:: then GOOG - :tick_Err::

I then edited the VB in fUtilities to make .StatusText <> 200 alteration. Test again. still get ::tick_err::

So on one PC where I do have admin ability and could have done stupide stuff to break underlying code bases required to make the addin work it is not functioning; but even on my corporate machine that is totally locked down; I also cant get it to work.


I'm not going to be too much help on why your struggling to get yours to work I'm afraid, but I just thought I'd offer up the news earlier that I'd got it going from scratch using those steps..

Cheers,

Itsallaguess

innocuous
Posts: 27
Joined: September 19th, 2020, 6:34 pm
Has thanked: 8 times
Been thanked: 3 times

Re: Share Prices in Excel - NEW update

#419767

Postby innocuous » June 15th, 2021, 7:18 pm

Thanks for your help anyway....now to see if I can get the stupid MS Stocks feature to search for funds.

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

#419771

Postby Itsallaguess » June 15th, 2021, 7:29 pm

innocuous wrote:
...now to see if I can get the stupid MS Stocks feature to search for funds.


If you don't get any joy with the MS Stocks route, then we put a fund sheet on the HYPTUSS tool which will fetch fund prices and currencies for you, if that might help -

https://i.imgur.com/JaDbiif.png

You just have to manually fill the first two columns in, and the tool will fetch the relevant prices and currencies if the fund symbol is correct.

The tool can be downloaded from here if this is something that might interest you - http://lemonfoolfinancialsoftware.weebly.com/hyp-top-up.html

If it's useful, and you'd prefer to use the relevant VBA for this particular functionality in your own spreadsheets, then the VBA password is 'pleaseletmein' and we'd be happy for you to re-use the code elsewhere if it helps...

Cheers,

Itsallaguess

Darka
Lemon Slice
Posts: 773
Joined: November 4th, 2016, 2:18 pm
Has thanked: 1819 times
Been thanked: 704 times

Re: Share Prices in Excel - NEW update

#419776

Postby Darka » June 15th, 2021, 7:49 pm

This might or might not be useful.

If you have a Microsoft 365 subscription (I do) then it has built in Stock Data types which are excellent.

https://support.microsoft.com/en-us/off ... 3cf07fbc54

I've just tried it with my HYP and IT portfolio and it worked for both, quite impressed really.

Unfortunately it doesn't include any dividend information at all, but I use Dividend Data for that at the moment anyway - have to enter this into my spreadsheet manually.

kiloran
Lemon Quarter
Posts: 4092
Joined: November 4th, 2016, 9:24 am
Has thanked: 3233 times
Been thanked: 2827 times

Re: Share Prices in Excel - NEW update

#419880

Postby kiloran » June 16th, 2021, 10:03 am

innocuous wrote:
innocuous wrote:
Itsallaguess wrote:I've just downloaded a fresh copy of the XLA add-in from here -

http://lemonfoolfinancialsoftware.weebly.com/share-prices-in-excel.html

I've opened a new Excel workbook (Windows 10 / Office 2007), with the following VBA references set -

  • Visual Basic For Applications
  • Microsoft Excel 12.0 Object Library
  • OLE Automation
  • Microsoft Office 12.0 Object Library

I've then activated the downloaded add-in via Excel Options / Add-ins / Manage Excel Add-ins, and made sure that the .xla file is added to the add-in references and activated.

I've then entered 'VOD.L' into Cell E7, and in Cell G7, I've typed the formula '=getdata(E7)', and pressed return.

At first, Cell G7 contained the text '::queued::', but when I right-clicked the cell, I got the option to 'Refresh prices once', and when I selected that option, Cell G7 then refreshed to display the correct Vodafone price of 131.28p

Cheers,

Itsallaguess


Thanks for checking - I am going to try it again totally fresh. I will also try on another PC and see if that works or not. Back shortly with the results. :-)


This is weird. So just used my standard work laptop win 10 build with Office 365 pro. Downloaded addin from your link; Activated addin in Excel; Shutdown Excel; Open new book and test VOD.L - ::tick_err:: then GOOG - :tick_Err::

I then edited the VB in fUtilities to make .StatusText <> 200 alteration. Test again. still get ::tick_err::

So on one PC where I do have admin ability and could have done stupide stuff to break underlying code bases required to make the addin work it is not functioning; but even on my corporate machine that is totally locked down; I also cant get it to work.

I just tried this and found exactly the same problem, using Windows 10 and Excel 2016. I did notice that the file at http://lemonfoolfinancialsoftware.weebl ... excel.html is tlfaddin..xla (two dots) rather than the expected tlfaddin.xla but correcting this made no difference.

I'll try to look at it over the next few days

--kiloran

kiloran
Lemon Quarter
Posts: 4092
Joined: November 4th, 2016, 9:24 am
Has thanked: 3233 times
Been thanked: 2827 times

Re: Share Prices in Excel - NEW update

#419942

Postby kiloran » June 16th, 2021, 12:37 pm

kiloran wrote:
innocuous wrote:
innocuous wrote:
Thanks for checking - I am going to try it again totally fresh. I will also try on another PC and see if that works or not. Back shortly with the results. :-)


This is weird. So just used my standard work laptop win 10 build with Office 365 pro. Downloaded addin from your link; Activated addin in Excel; Shutdown Excel; Open new book and test VOD.L - ::tick_err:: then GOOG - :tick_Err::

I then edited the VB in fUtilities to make .StatusText <> 200 alteration. Test again. still get ::tick_err::

So on one PC where I do have admin ability and could have done stupide stuff to break underlying code bases required to make the addin work it is not functioning; but even on my corporate machine that is totally locked down; I also cant get it to work.

I just tried this and found exactly the same problem, using Windows 10 and Excel 2016. I did notice that the file at http://lemonfoolfinancialsoftware.weebl ... excel.html is tlfaddin..xla (two dots) rather than the expected tlfaddin.xla but correcting this made no difference.

I'll try to look at it over the next few days

--kiloran

Right, I've got a fix, though I don't yet understand the details so may come up with a better fix.

I tried on a laptop with Windows 10 and Excel 2010 and the tlfaddin.xla worked fine, but on a laptop with Excel 2016, it failed.

Here's the fix for Excel 2016 (and hopefully Office 365).
  1. From the Developer menu, open the Visual Basic editor
  2. In the VB editor, navigate to tlfaddin/Modules/fUtilities
  3. Change the line
    If .StatusText <> "OK" Then GoTo ServerErrorHandler
    to
    If .StatusText <> "" Then GoTo ServerErrorHandler
  4. File/Save

I don't yet know why Excel 2016 does not return a value for myXML.StatusText but I'll investigate

Hope this helps

--kiloran

innocuous
Posts: 27
Joined: September 19th, 2020, 6:34 pm
Has thanked: 8 times
Been thanked: 3 times

Re: Share Prices in Excel - NEW update

#420670

Postby innocuous » June 19th, 2021, 7:19 am

innocuous wrote:This is weird. So just used my standard work laptop win 10 build with Office 365 pro. Downloaded addin from your link; Activated addin in Excel; Shutdown Excel; Open new book and test VOD.L - ::tick_err:: then GOOG - :tick_Err::

I then edited the VB in fUtilities to make .StatusText <> 200 alteration. Test again. still get ::tick_err::

So on one PC where I do have admin ability and could have done stupide stuff to break underlying code bases required to make the addin work it is not functioning; but even on my corporate machine that is totally locked down; I also cant get it to work.


Further update - had to rebuild my wife's laptop (Win 10 Pro, did a reset - all apps and data), then reinstalled Office 365 x65 (Home use programme). Tried the addin as per the above description and it still stayed in pending. Is there anything that could be interrupting this from a firewall perspective?

kiloran
Lemon Quarter
Posts: 4092
Joined: November 4th, 2016, 9:24 am
Has thanked: 3233 times
Been thanked: 2827 times

Re: Share Prices in Excel - NEW update

#420769

Postby kiloran » June 19th, 2021, 4:40 pm

innocuous wrote:
innocuous wrote:This is weird. So just used my standard work laptop win 10 build with Office 365 pro. Downloaded addin from your link; Activated addin in Excel; Shutdown Excel; Open new book and test VOD.L - ::tick_err:: then GOOG - :tick_Err::

I then edited the VB in fUtilities to make .StatusText <> 200 alteration. Test again. still get ::tick_err::

So on one PC where I do have admin ability and could have done stupide stuff to break underlying code bases required to make the addin work it is not functioning; but even on my corporate machine that is totally locked down; I also cant get it to work.


Further update - had to rebuild my wife's laptop (Win 10 Pro, did a reset - all apps and data), then reinstalled Office 365 x65 (Home use programme). Tried the addin as per the above description and it still stayed in pending. Is there anything that could be interrupting this from a firewall perspective?

Apologies, it looks like I've suffered a senior moment. I've been distracted by other issues lately and didn't concentrate enough when I investigated this issue.

Eventide suggested earlier changing:
If .StatusText <> "OK"
to:
If .Status <> 200

I inadvertently and incorrectly read the change as If .StatusText <> 200 and this did not fix my problem. When I made the change correctly, the Add-in now works correctly in Windows 10 and Excel 2016. I have not found any explanation why Excel 2016 returns an empty field for StatusText, but the Status field is correctly populated.

I've updated the add-in to tlsAddin-rev1.xla and it is available at http://lemonfoolfinancialsoftware.weebl ... excel.html

Many apologies for any confusion I have caused

--kiloran

jaizan
Lemon Slice
Posts: 385
Joined: September 1st, 2018, 10:21 pm
Has thanked: 212 times
Been thanked: 113 times

Re: Share Prices in Excel - NEW update

#421906

Postby jaizan » June 24th, 2021, 12:57 pm

Thanks.

I installed this last week and it worked OK. It is no longer working, despite Excel saying the add in is active. I've tried several tickers, including opening spreadsheets open last week and new ones. The Yahoo website shows data for the respective stocks.
Any ideas where I'm going wrong ?

kiloran
Lemon Quarter
Posts: 4092
Joined: November 4th, 2016, 9:24 am
Has thanked: 3233 times
Been thanked: 2827 times

Re: Share Prices in Excel - NEW update

#421914

Postby kiloran » June 24th, 2021, 1:16 pm

jaizan wrote:Thanks.

I installed this last week and it worked OK. It is no longer working, despite Excel saying the add in is active. I've tried several tickers, including opening spreadsheets open last week and new ones. The Yahoo website shows data for the respective stocks.
Any ideas where I'm going wrong ?

What is the "this" that you installed last week? tlfAddin.xla or tlfAddin-Rev1.xla?
In what way is it not working?
Which version of Excel?

--kiloran

jaizan
Lemon Slice
Posts: 385
Joined: September 1st, 2018, 10:21 pm
Has thanked: 212 times
Been thanked: 113 times

Re: Share Prices in Excel - NEW update

#422790

Postby jaizan » June 27th, 2021, 10:49 am

I have tlsAddin-Rev1.xla

Excel version is Home & Student 2010. I presume it is possible for this add in to work with this version, as it worked just fine when I first installed it a couple of weeks ago.

Currently it shows the Add In as active, but Excel no longer recognizes the getdata() function. That includes the original spreadsheets which were working and various modifications since.

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

#423086

Postby eventide » June 28th, 2021, 10:45 am

kiloran wrote:Apologies, it looks like I've suffered a senior moment. I've been distracted by other issues lately and didn't concentrate enough when I investigated this issue.

Eventide suggested earlier changing:
If .StatusText <> "OK"
to:
If .Status <> 200

I inadvertently and incorrectly read the change as If .StatusText <> 200 and this did not fix my problem. When I made the change correctly, the Add-in now works correctly in Windows 10 and Excel 2016. I have not found any explanation why Excel 2016 returns an empty field for StatusText, but the Status field is correctly populated.

I've updated the add-in to tlsAddin-rev1.xla and it is available at http://lemonfoolfinancialsoftware.weebl ... excel.html

Many apologies for any confusion I have caused

--kiloran


Easy mistake to make.

FWIW, I have just reviewed the VBA for the addin (including the revision) and I'm satisfied that it still works exactly as intended, ie any issues that users are having are not due to a revision to excel, the VBA, or yahoo for that matter.

kiloran
Lemon Quarter
Posts: 4092
Joined: November 4th, 2016, 9:24 am
Has thanked: 3233 times
Been thanked: 2827 times

Re: Share Prices in Excel - NEW update

#423147

Postby kiloran » June 28th, 2021, 1:25 pm

jaizan wrote:I have tlsAddin-Rev1.xla

Excel version is Home & Student 2010. I presume it is possible for this add in to work with this version, as it worked just fine when I first installed it a couple of weeks ago.

Currently it shows the Add In as active, but Excel no longer recognizes the getdata() function. That includes the original spreadsheets which were working and various modifications since.

I normally use Excel 2016 and the add-in works fine. I have another laptop with Excel Home and Student 2010 so tried that and found similar problems to you. I then realised that the Excel 2010 I installed is 64-bit, so I wonder if that might be an issue? It's recommended that you use 32-bit Excel (even on a 64-bit OS) unless 64-bit is specifically required. I deliberately installed the 64-bit version to test a specific problem unrelated to this add-in.

So, is your Excel 32 or 64 bit? See FILE/HELP for your version

I'll investigate further when I get time, the 32 vs 64 bit may be a red herring

--kiloran

jaizan
Lemon Slice
Posts: 385
Joined: September 1st, 2018, 10:21 pm
Has thanked: 212 times
Been thanked: 113 times

Re: Share Prices in Excel - NEW update

#423229

Postby jaizan » June 28th, 2021, 8:10 pm

I have 32 bit Excel, running on Windows 10 (64 bit)

bobby
Posts: 1
Joined: August 5th, 2021, 10:10 pm

Re: Share Prices in Excel - NEW update

#527658

Postby bobby » September 5th, 2022, 3:00 pm

Hello

I was finding this Yahoo prices add-in Tlfaddin-Rev1 very helpful until last week (though I always had to disable then re-enable the add-in after each pc restart).

This week unfortunately I can't get it to work at all! Excel gives me a 'security warning' that says it's blocked the macro as 'source untrusted'. I've been into the MS Trust Centre and included the add-in within my Trusted Locations (from my Downloads folder) but it still blocks the macro.

I'm running Office Professional Plus 2016 but all this 'security/trust' complexity is beyond my competence! Are there any other settings I can try to enable access?

Many thanks

kiloran
Lemon Quarter
Posts: 4092
Joined: November 4th, 2016, 9:24 am
Has thanked: 3233 times
Been thanked: 2827 times

Re: Share Prices in Excel - NEW update

#527977

Postby kiloran » September 6th, 2022, 2:58 pm

bobby wrote:Hello

I was finding this Yahoo prices add-in Tlfaddin-Rev1 very helpful until last week (though I always had to disable then re-enable the add-in after each pc restart).

This week unfortunately I can't get it to work at all! Excel gives me a 'security warning' that says it's blocked the macro as 'source untrusted'. I've been into the MS Trust Centre and included the add-in within my Trusted Locations (from my Downloads folder) but it still blocks the macro.

I'm running Office Professional Plus 2016 but all this 'security/trust' complexity is beyond my competence! Are there any other settings I can try to enable access?

Many thanks

I have the add-in installed but I'm not in the habit of using it. I'm using Office Home and Student 2016 on Windows 10 using the default Microsoft security. I tried a few queries with the add-in and it worked fine (with .xlsx filetype). I then disabled the Trust Centre settings and tried again. I got a security warning "application add-ins have been disabled" with a button to "Enable Content". I clicked this and it worked OK.

I'm not sure what to suggest to solve your problem. Sorry!

--kiloran


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 7 guests