Donate to Remove ads

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

Thanks to MrFahrenheit,SalvorHardin,Anonymous,johnhemming,Anonymous, for Donating to support the site

Share Prices in Excel - NEW update

Discussions regarding financial software
kiloran
Lemon Quarter
Posts: 2580
Joined: November 4th, 2016, 9:24 am
Has thanked: 1177 times
Been thanked: 1244 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: 177
Joined: November 5th, 2016, 12:02 am
Has thanked: 125 times
Been thanked: 36 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: 5369
Joined: November 4th, 2016, 1:16 pm
Has thanked: 2145 times
Been thanked: 3933 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: 415
Joined: November 4th, 2016, 9:12 am
Has thanked: 51 times
Been thanked: 72 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: 177
Joined: November 5th, 2016, 12:02 am
Has thanked: 125 times
Been thanked: 36 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: 14 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: 2580
Joined: November 4th, 2016, 9:24 am
Has thanked: 1177 times
Been thanked: 1244 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 Slice
Posts: 708
Joined: November 5th, 2016, 9:37 am
Has thanked: 102 times
Been thanked: 337 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
Lemon Pip
Posts: 75
Joined: October 24th, 2017, 3:29 pm
Has thanked: 2 times
Been thanked: 69 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: 1
Joined: June 6th, 2020, 4:50 am

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: 1085
Joined: November 4th, 2016, 10:15 am
Has thanked: 62 times
Been thanked: 410 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: 1424
Joined: November 4th, 2016, 11:12 am
Has thanked: 833 times
Been thanked: 417 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 Pip
Posts: 64
Joined: November 23rd, 2019, 4:59 pm
Been thanked: 29 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
Lemon Pip
Posts: 75
Joined: October 24th, 2017, 3:29 pm
Has thanked: 2 times
Been thanked: 69 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: 1424
Joined: November 4th, 2016, 11:12 am
Has thanked: 833 times
Been thanked: 417 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: 1085
Joined: November 4th, 2016, 10:15 am
Has thanked: 62 times
Been thanked: 410 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 ?


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 9 guests