Page 6 of 8

Re: Share Prices in Excel - NEW update

Posted: January 26th, 2020, 11:39 pm
by kiloran
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

Re: Share Prices in Excel - NEW update

Posted: February 6th, 2020, 1:59 am
by midgesgalore
Hi Itsallaguess,
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

Re: Share Prices in Excel - NEW update

Posted: February 6th, 2020, 4:32 am
by Itsallaguess
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

Re: Share Prices in Excel - NEW update

Posted: February 6th, 2020, 12:18 pm
by spiderbill
midgesgalore wrote:Can you or any other kind soul point me in the right direction?


Hi midgesgalore

Just in case it helps - as well as the indexes which itsallaguess has already supplied you can also get exchange rates by the same method, e.g the US, EURO and Canadian rates to the Pound are:

GBPUSD=X
GBPEUR=X
GBPCAD=X

and the price of Gold is
GC=F

cheers
Spiderbill

Re: Share Prices in Excel - NEW update

Posted: February 6th, 2020, 12:31 pm
by midgesgalore
Thanks IAAG & spiderbill

As Christoph Walz famously said, "That's a bingo"

midgesgalore

Re: Share Prices in Excel - NEW update

Posted: February 6th, 2020, 3:19 pm
by ffacoffipawb64
Can you get previous days' prices using getdata?

Also can you get something like last year's dividend per share?

Thanks

Re: Share Prices in Excel - NEW update

Posted: February 6th, 2020, 4:35 pm
by kiloran
ffacoffipawb64 wrote:Can you get previous days' prices using getdata?

Also can you get something like last year's dividend per share?

Thanks

Previous close is available. See: http://lemonfoolfinancialsoftware.weebl ... excel.html

dps is not available.... not in Yahoo, not a deficiency in the add-in

--kiloran

Re: Share Prices in Excel - NEW update

Posted: February 8th, 2020, 12:18 pm
by simoan
Just a quick note to warn holders of fixed income shares, bonds etc. that Yahoo no longer returns the current price for some, with the price returned stuck since October 2019. 66WS, LLPC and NWBD are affected. Obviously, I hold and since they are holdings I have no interest in selling so rarely monitor, I'm embarrassed to admit I didn't notice the incorrect price until yesterday. Ooops! Good news is that I'm a fair bit richer than my spreadsheet was showing :)

All the best, Si

Re: Share Prices in Excel - NEW update

Posted: May 28th, 2020, 11:36 am
by Scrooge
Does anyone use this add-in in Excel on a Mac?

I am getting "sPrices" errors and then it points me to the VBA code while asking for its password.

Thanks!

(I'll try it on the Windows machine too)

Re: Share Prices in Excel - NEW update

Posted: May 29th, 2020, 11:40 am
by eventide
Addin in Excel on Mac doesnt work, as the VBA needs references to certain microsoft libraries (xml from memory)

Re: Share Prices in Excel - NEW update

Posted: June 6th, 2020, 5:04 am
by kltrader
I'd really encourage looking at solutions outside Excel. The Excel/VBA model is great for v simple solutions but for any serious time-series analysis, it is extremely clunky and difficult to use. A very simple alternative is Pandas (which was originally an in-house tool built for a hedge fund) and is extremely fast and easy to learn. Using a basic Pandas tutorial and some sample intraday data I was building simple models with 2 days.

Re: Share Prices in Excel - NEW update

Posted: June 29th, 2020, 2:59 pm
by scrumpyjack
I've been using tflAddin for ages with no problems. Recently subscribed to Office 365 and I cannot get it to work with Excel (365) but it continues to work fine in Excel 2007.

Any ideas anyone?

Thanks

Re: Share Prices in Excel - NEW update

Posted: June 29th, 2020, 3:35 pm
by ReformedCharacter
scrumpyjack wrote:I've been using tflAddin for ages with no problems. Recently subscribed to Office 365 and I cannot get it to work with Excel (365) but it continues to work fine in Excel 2007.

Any ideas anyone?

Thanks

Excel 365 won't run the required code, only desktop versions will.

RC

Re: Share Prices in Excel - NEW update

Posted: July 9th, 2020, 1:47 pm
by Newroad
Hi All,

On a related note, MS are beta-testing an Excel Stock History function with some O365 users

https://redmondmag.com/articles/2020/06/15/microsoft-releases-money-in-excel.aspx#:~:text=Stock%20History%20is%20an%20Excel,charts%20and%20graphs%2C%20if%20wanted.&text=Microsoft%20pulls%20the%20stock%20information,a%20financial%20market%20data%20provider.

Alas, though an O365 user, I'm not one of their beta-testers :(

Regards, Newroad

Re: Share Prices in Excel - NEW update

Posted: July 22nd, 2020, 10:43 am
by eventide
ReformedCharacter wrote:
scrumpyjack wrote:I've been using tflAddin for ages with no problems. Recently subscribed to Office 365 and I cannot get it to work with Excel (365) but it continues to work fine in Excel 2007.

Any ideas anyone?

Thanks

Excel 365 won't run the required code, only desktop versions will.

RC



This is incorrect. I wrote the add in and am using it with Office 365 as are many others.

If its not working for you, look up this thread for some adjustments which should be made. Page 4 I think.

Re: Share Prices in Excel - NEW update

Posted: July 22nd, 2020, 11:12 am
by ReformedCharacter
eventide wrote:
ReformedCharacter wrote:
scrumpyjack wrote:I've been using tflAddin for ages with no problems. Recently subscribed to Office 365 and I cannot get it to work with Excel (365) but it continues to work fine in Excel 2007.

Any ideas anyone?

Thanks

Excel 365 won't run the required code, only desktop versions will.

RC



This is incorrect. I wrote the add in and am using it with Office 365 as are many others.

If its not working for you, look up this thread for some adjustments which should be made. Page 4 I think.

Ah, I see it's the free online version that will not run VBA.

RC

Re: Share Prices in Excel - NEW update

Posted: July 22nd, 2020, 2:32 pm
by scrumpyjack
eventide wrote:
ReformedCharacter wrote:
scrumpyjack wrote:I've been using tflAddin for ages with no problems. Recently subscribed to Office 365 and I cannot get it to work with Excel (365) but it continues to work fine in Excel 2007.

Any ideas anyone?

Thanks

Excel 365 won't run the required code, only desktop versions will.

RC



This is incorrect. I wrote the add in and am using it with Office 365 as are many others.

If its not working for you, look up this thread for some adjustments which should be made. Page 4 I think.


Thanks, that fixed it!

Re: Share Prices in Excel - NEW update

Posted: July 23rd, 2020, 1:12 pm
by wissamzeenni
I have been using the addin for a couple of years however as of last week, I am always getting ticker error returned.
The strange thing is that it works again if I only try and parse a limited number of tickers. few 10s compared to the 100s..
Anyone experienced this or might have an idea whats causing it ?

Re: Share Prices in Excel - NEW update

Posted: January 5th, 2021, 10:07 pm
by Gostevie
Dear Fools,

Sorry to resurrect an old thread but I have a query and this seems to be the most relevant thread on which to ask it.

For quite a few years I have been using the "tlfAddin.xla" piece of magic and its predecessor to update prices in my Excel spreadsheets, but just recently whenever I try to use it every line brings up the error message:

::tick_err::

Can anybody advise me as to what might be causing this and if there is anything I can so to rectify it? I am using 'Microsoft Office Home and Student 2019' on Windows 10.

Many thanks in advance,

Gostevie

Re: Share Prices in Excel - NEW update

Posted: January 5th, 2021, 10:30 pm
by IanTHughes
Gostevie wrote:Sorry to resurrect an old thread but I have a query and this seems to be the most relevant thread on which to ask it.

For quite a few years I have been using the "tlfAddin.xla" piece of magic and its predecessor to update prices in my Excel spreadsheets, but just recently whenever I try to use it every line brings up the error message:

::tick_err::

Can anybody advise me as to what might be causing this and if there is anything I can so to rectify it? I am using 'Microsoft Office Home and Student 2019' on Windows 10.

I have not used the Add-In "tlfAddin.xla" - or do you mean tfsAddin.xla - that you mention, but could the stock data function's disappearance be due to its function being replaced by the recently released function STOCKHISTORY: https://sfmagazine.com/post-entry/augus ... nd%20bonds.


Ian