Donate to Remove ads

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

Thanks to Anonymous,johnhemming,Anonymous,Rhyd6,dredd0, for Donating to support the site

Share Prices in Excel - NEW update

Discussions regarding financial software
kiloran
Lemon Quarter
Posts: 2508
Joined: November 4th, 2016, 9:24 am
Has thanked: 1111 times
Been thanked: 1204 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: 175
Joined: November 5th, 2016, 12:02 am
Has thanked: 116 times
Been thanked: 35 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: 5239
Joined: November 4th, 2016, 1:16 pm
Has thanked: 2075 times
Been thanked: 3752 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: 405
Joined: November 4th, 2016, 9:12 am
Has thanked: 47 times
Been thanked: 69 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: 175
Joined: November 5th, 2016, 12:02 am
Has thanked: 116 times
Been thanked: 35 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: 2508
Joined: November 4th, 2016, 9:24 am
Has thanked: 1111 times
Been thanked: 1204 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: 690
Joined: November 5th, 2016, 9:37 am
Has thanked: 99 times
Been thanked: 330 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: 71
Joined: October 24th, 2017, 3:29 pm
Has thanked: 2 times
Been thanked: 68 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: 1017
Joined: November 4th, 2016, 10:15 am
Has thanked: 48 times
Been thanked: 386 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: 1372
Joined: November 4th, 2016, 11:12 am
Has thanked: 798 times
Been thanked: 394 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


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 5 guests