Donate to Remove ads

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

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

Share Prices in Excel - NEW update

Discussions regarding financial software
kiloran
Lemon Quarter
Posts: 4088
Joined: November 4th, 2016, 9:24 am
Has thanked: 3224 times
Been thanked: 2824 times

Re: Share Prices in Excel - NEW update

#280190

Postby kiloran » January 26th, 2020, 11:39 pm

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

midgesgalore
2 Lemon pips
Posts: 246
Joined: November 5th, 2016, 12:02 am
Has thanked: 266 times
Been thanked: 69 times

Re: Share Prices in Excel - NEW update

#282607

Postby midgesgalore » February 6th, 2020, 1:59 am

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

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

Re: Share Prices in Excel - NEW update

#282610

Postby Itsallaguess » February 6th, 2020, 4:32 am

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

spiderbill
Lemon Slice
Posts: 537
Joined: November 4th, 2016, 9:12 am
Has thanked: 151 times
Been thanked: 182 times

Re: Share Prices in Excel - NEW update

#282720

Postby spiderbill » February 6th, 2020, 12:18 pm

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

midgesgalore
2 Lemon pips
Posts: 246
Joined: November 5th, 2016, 12:02 am
Has thanked: 266 times
Been thanked: 69 times

Re: Share Prices in Excel - NEW update

#282723

Postby midgesgalore » February 6th, 2020, 12:31 pm

Thanks IAAG & spiderbill

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

midgesgalore

ffacoffipawb64
Lemon Pip
Posts: 71
Joined: May 26th, 2019, 11:54 am
Has thanked: 10 times
Been thanked: 17 times

Re: Share Prices in Excel - NEW update

#282758

Postby ffacoffipawb64 » February 6th, 2020, 3:19 pm

Can you get previous days' prices using getdata?

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

Thanks

kiloran
Lemon Quarter
Posts: 4088
Joined: November 4th, 2016, 9:24 am
Has thanked: 3224 times
Been thanked: 2824 times

Re: Share Prices in Excel - NEW update

#282773

Postby kiloran » February 6th, 2020, 4:35 pm

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

simoan
Lemon Quarter
Posts: 2081
Joined: November 5th, 2016, 9:37 am
Has thanked: 461 times
Been thanked: 1448 times

Re: Share Prices in Excel - NEW update

#283114

Postby simoan » February 8th, 2020, 12:18 pm

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

Scrooge
Posts: 1
Joined: May 28th, 2020, 11:15 am
Has thanked: 1 time

Re: Share Prices in Excel - NEW update

#312985

Postby Scrooge » May 28th, 2020, 11:36 am

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)

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

#313317

Postby eventide » May 29th, 2020, 11:40 am

Addin in Excel on Mac doesnt work, as the VBA needs references to certain microsoft libraries (xml from memory)

kltrader
Posts: 12
Joined: June 6th, 2020, 4:50 am
Been thanked: 5 times

Re: Share Prices in Excel - NEW update

#315688

Postby kltrader » June 6th, 2020, 5:04 am

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.

scrumpyjack
Lemon Quarter
Posts: 4791
Joined: November 4th, 2016, 10:15 am
Has thanked: 603 times
Been thanked: 2659 times

Re: Share Prices in Excel - NEW update

#322560

Postby scrumpyjack » June 29th, 2020, 2:59 pm

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

ReformedCharacter
Lemon Quarter
Posts: 3120
Joined: November 4th, 2016, 11:12 am
Has thanked: 3572 times
Been thanked: 1509 times

Re: Share Prices in Excel - NEW update

#322572

Postby ReformedCharacter » June 29th, 2020, 3:35 pm

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

Newroad
Lemon Quarter
Posts: 1087
Joined: November 23rd, 2019, 4:59 pm
Has thanked: 17 times
Been thanked: 340 times

Re: Share Prices in Excel - NEW update

#324848

Postby Newroad » July 9th, 2020, 1:47 pm

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

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

#327889

Postby eventide » July 22nd, 2020, 10:43 am

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.

ReformedCharacter
Lemon Quarter
Posts: 3120
Joined: November 4th, 2016, 11:12 am
Has thanked: 3572 times
Been thanked: 1509 times

Re: Share Prices in Excel - NEW update

#327903

Postby ReformedCharacter » July 22nd, 2020, 11:12 am

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

scrumpyjack
Lemon Quarter
Posts: 4791
Joined: November 4th, 2016, 10:15 am
Has thanked: 603 times
Been thanked: 2659 times

Re: Share Prices in Excel - NEW update

#327950

Postby scrumpyjack » July 22nd, 2020, 2:32 pm

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!

wissamzeenni
Posts: 2
Joined: May 10th, 2018, 2:23 pm
Has thanked: 1 time

Re: Share Prices in Excel - NEW update

#328172

Postby wissamzeenni » July 23rd, 2020, 1:12 pm

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 ?

Gostevie
2 Lemon pips
Posts: 222
Joined: November 4th, 2016, 11:35 am
Has thanked: 838 times
Been thanked: 158 times

Re: Share Prices in Excel - NEW update

#373628

Postby Gostevie » January 5th, 2021, 10:07 pm

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

IanTHughes
Lemon Quarter
Posts: 1789
Joined: May 2nd, 2018, 12:01 pm
Has thanked: 730 times
Been thanked: 1117 times

Re: Share Prices in Excel - NEW update

#373636

Postby IanTHughes » January 5th, 2021, 10:30 pm

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


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: Bing [Bot] and 2 guests