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.pngYahoo page for FTSE100, showing the relevant ticker to use in brackets -
https://i.imgur.com/dg5dKVA.pngHope 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.htmldps 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
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