Remove ads

Introducing the LemonFools Personal Finance Calculators

Share Prices in Excel - NEW update

Discussions regarding financial software
kiloran
Lemon Quarter
Posts: 1690
Joined: November 4th, 2016, 9:24 am
Has thanked: 389 times
Been thanked: 713 times

Re: Share Prices in Excel - NEW update

#123187

Postby kiloran » March 8th, 2018, 2:23 pm

will89 wrote:Hi Kiloran,
Excel for Mac does have VBA, but I think you're right in suggesting that a basic macro might work better.
I've tried downloading the linked file, but I can't seem to make it work even on my Windows PC, do you need to copy and paste the code in VBA that's assigned to the sheet and then create a button in my own file that uses that code? I couldn't see a separate macro module in the file I downloaded. Basically, no idea how to get the functionality from your file into mine.

OK, let's stick with Windows for the moment (I don't have access to a Mac). Download the file simple_yahoo_price_scrape_v1-1.xls and open it. You should see a button "Get Yahoo Prices". Click on that and the prices in column B should update. If that is OK, you could leave the sheet as-is and use the VLOOKUP function to extract the data to a new sheet or workbook.

If that works OK, then try it on the Mac and let us know if it works.

If you want to use the VBA code in another workbook, open the Visual Basic editor and you should see the code attached to Sheet1. You can copy and adapt the code as you wish.

will89 wrote:Apologies if I'm being stupid...

Hey, I've got a monopoly on that.

--kiloran

will89
Lemon Pip
Posts: 84
Joined: November 7th, 2016, 9:33 am
Has thanked: 2 times
Been thanked: 27 times

Re: Share Prices in Excel - NEW update

#123194

Postby will89 » March 8th, 2018, 2:30 pm

kiloran wrote:
will89 wrote:Apologies if I'm being stupid...

Hey, I've got a monopoly on that.

--kiloran


I don't think so!

I've gone for the 2nd option, pasting the VBA code into my own workbook, but I'm then scuppered by my apparent lack of VBA knowledge. Mainly, how do I program a new button in my workbook to carry out the same task as in yours, and where in the VBA code do I edit which columns I want it to pull the EPIC from and paste the price to. With previous macros it has been really obvious where to change column 'A' to 'K' etc, but my brain seems to be failing me here.

Bloody Yahoo... This used to be nice and easy! Literally the only automated thing that I require in my whole huge spreadsheet is an import of prices to a column, others must be much more complicated!

eventide
Lemon Pip
Posts: 58
Joined: October 24th, 2017, 3:29 pm
Has thanked: 2 times
Been thanked: 55 times

Re: Share Prices in Excel - NEW update

#123441

Postby eventide » March 9th, 2018, 11:48 am

vrdiver wrote:Question: is there a user manual / url reference / instructions / idiot guide that I can go to to show me what other data is available and how to get it?
VRD


This was the original thread with info on fields available.

viewtopic.php?f=27&t=8229


I have also mentioned elsewhere that at some point I will have a new version of this add-in which adds functionality, including some menu based help on function use, and also including looking up fund prices from Morningstar via ISIN and an ability to pull the last 12m dividends schedules (ex date, paydate, amounts) and any currently declared but not yet ex (or paid) dividends for UK stocks off the dividenddata website.

No word on timing though, sadly I'm busy with real world stuff.

kiloran
Lemon Quarter
Posts: 1690
Joined: November 4th, 2016, 9:24 am
Has thanked: 389 times
Been thanked: 713 times

Re: Share Prices in Excel - NEW update

#123644

Postby kiloran » March 9th, 2018, 7:54 pm

will89 wrote:I've gone for the 2nd option, pasting the VBA code into my own workbook, but I'm then scuppered by my apparent lack of VBA knowledge. Mainly, how do I program a new button in my workbook to carry out the same task as in yours, and where in the VBA code do I edit which columns I want it to pull the EPIC from and paste the price to. With previous macros it has been really obvious where to change column 'A' to 'K' etc, but my brain seems to be failing me here.


To add a new button, just use the Draw facility in Excel to add any shape you like.

To link the button to a macro, first ensure that the macro is not a private subroutine, so in the macro in Sheet1, change
Private Sub CommandButton1_Click()
to:
Sub CommandButton1_Click()

Then right-click on the new button, select "Assign Macro" and select the macro Sheet1.CommandButton1_click, then click on OK

To edit which columns are used, here, for example, is the code to initially clear the data from column B:

For rowsdown = 1 To lastrow
Cells(rowsdown, 2) = ""
Next rowsdown


The Cells function is Cells(row,column), so column 2 corresponds to Column B
Make appropriate changes to other Cells() functions as required.

Hope this helps

--kiloran

will89
Lemon Pip
Posts: 84
Joined: November 7th, 2016, 9:33 am
Has thanked: 2 times
Been thanked: 27 times

Re: Share Prices in Excel - NEW update

#123725

Postby will89 » March 10th, 2018, 10:10 am

kiloran wrote:
will89 wrote:I've gone for the 2nd option, pasting the VBA code into my own workbook, but I'm then scuppered by my apparent lack of VBA knowledge. Mainly, how do I program a new button in my workbook to carry out the same task as in yours, and where in the VBA code do I edit which columns I want it to pull the EPIC from and paste the price to. With previous macros it has been really obvious where to change column 'A' to 'K' etc, but my brain seems to be failing me here.


To add a new button, just use the Draw facility in Excel to add any shape you like.

To link the button to a macro, first ensure that the macro is not a private subroutine, so in the macro in Sheet1, change
Private Sub CommandButton1_Click()
to:
Sub CommandButton1_Click()

Then right-click on the new button, select "Assign Macro" and select the macro Sheet1.CommandButton1_click, then click on OK

To edit which columns are used, here, for example, is the code to initially clear the data from column B:

For rowsdown = 1 To lastrow
Cells(rowsdown, 2) = ""
Next rowsdown


The Cells function is Cells(row,column), so column 2 corresponds to Column B
Make appropriate changes to other Cells() functions as required.

Hope this helps

--kiloran


That's perfect, now works excellently on my Windows PC ;)

Almost there on Mac, but not quite... I think it could be to do with ActiveX.

If I press 'play' within VBA, I get the error: 'Run time error 429. ActiveX component can't create object.'
If I assign the macro the a button, I get the error: 'License information for this component not found. You do not have an appropriate license to use this functionality in the design environment.'

A quick google doesn't seem to offer much help sadly.

will89
Lemon Pip
Posts: 84
Joined: November 7th, 2016, 9:33 am
Has thanked: 2 times
Been thanked: 27 times

Re: Share Prices in Excel - NEW update

#123726

Postby will89 » March 10th, 2018, 10:13 am

I presume the top answer in this thread may be related to the issue:

https://stackoverflow.com/questions/437 ... el-for-mac

Don't worry if it's not going to work, I'll just have to think of another way. Frustratingly Excel for Mac has a fantastic 'stocks' addin that will update prices by itself, but since Yahoo stopped the feed it doesn't work for the UK :(

scrumpyjack
Lemon Slice
Posts: 379
Joined: November 4th, 2016, 10:15 am
Has thanked: 16 times
Been thanked: 105 times

Re: Share Prices in Excel - NEW update

#134354

Postby scrumpyjack » April 24th, 2018, 10:39 am

Now getting a very strange problem with the GETDATA function.

It works perfectly with all my tickers (about 30 of them) except for Alliance Trust. For ATST sometimes it works but increasingly now it returns Ticker Error

Is this a problem with the data source and is there a work round?

Any ideas much appreciated

kiloran
Lemon Quarter
Posts: 1690
Joined: November 4th, 2016, 9:24 am
Has thanked: 389 times
Been thanked: 713 times

Re: Share Prices in Excel - NEW update

#134355

Postby kiloran » April 24th, 2018, 10:42 am

scrumpyjack wrote:Now getting a very strange problem with the GETDATA function.

It works perfectly with all my tickers (about 30 of them) except for Alliance Trust. For ATST sometimes it works but increasingly now it returns Ticker Error

Is this a problem with the data source and is there a work round?

Any ideas much appreciated

I strongly suspect it's a data source (Yahoo) error. See viewtopic.php?f=27&t=11105

--kiloran

scrumpyjack
Lemon Slice
Posts: 379
Joined: November 4th, 2016, 10:15 am
Has thanked: 16 times
Been thanked: 105 times

Re: Share Prices in Excel - NEW update

#134372

Postby scrumpyjack » April 24th, 2018, 11:15 am

Thanks yes it is Yahoo not working. If I go to the Yahoo page it has ATST.L in its lookup list but returns nothing when I select it!

Thanks

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

Re: Share Prices in Excel - NEW update

#138082

Postby wissamzeenni » May 10th, 2018, 2:27 pm

Hi, Great add-in, using it on a daily basis ! Thanks for all the efforts.

1 feature that I think is missing (or I could not figure it out yet!) is grabbing a share price for a particular date as an extra argument... For example =getdata(BOO.L,"close", "03/05/2017")

Not sure if this is feasible or already available....

eventide
Lemon Pip
Posts: 58
Joined: October 24th, 2017, 3:29 pm
Has thanked: 2 times
Been thanked: 55 times

Re: Share Prices in Excel - NEW update

#138246

Postby eventide » May 11th, 2018, 8:54 am

It is doable but a pia and is not planned by me rn

steveycarr
Posts: 1
Joined: February 6th, 2019, 10:48 pm
Has thanked: 1 time

Re: Share Prices in Excel - NEW update

#199440

Postby steveycarr » February 6th, 2019, 11:02 pm

Hi eventide,

I'm using your superb addin which so far is working a dream. You expressed that you have a way of incorporating equities/funds into excel in a similar manner. Can you advise how I can get started. I,ve done a bit of Vb programming in the past but I'm sort of stuck on how to get started. Any help would be much appreciated.

Steve

eventide
Lemon Pip
Posts: 58
Joined: October 24th, 2017, 3:29 pm
Has thanked: 2 times
Been thanked: 55 times

Re: Share Prices in Excel - NEW update

#199613

Postby eventide » February 7th, 2019, 2:53 pm

Its pretty messy, but a workable solution gets fund prices and NAVs (and whatever other stuff you want to scrape) from Morningstar.

MS have their own unique Token for each security they amass data on. So you have to programatically query the MS search function first, identify the token in the HTML, then pull the security page using the token. From there it is down to your scraping prowess to extract the data you want efficiently.

eg:

http://www.morningstar.co.uk/uk/funds/s ... F00000O7HT [&tab=1,2,3 etc]

where the id is the MS specific token, which you previously extracted by searching for a fund by ISIN. You only need to collect and store this token once (ie when a new request is made), then just store the token in a permanent dictionary.

The same thing works on stocks and investment trusts at MS, there is a wealth of data, miles better than yahoo finance, if you have access to the tokens. In fact I took a list of every security on the LSE and ran each ISIN through MS overnight to put each token I could find into a dictionary so I had a hardcopy store of their tokens, but unfortunately I dont have a list of every fund ISIN to do the same thing, which would make life a bit easier.

I have a working VBA test case but it is not going to be added into the tlf addin as it just complicates things. The problem is you can't batch say 200 securities as you can via the yahoo api, so it is slow to update, even if you are only doing it once daily as the NAV only changes once daily.

Good hunting.

eventide
Lemon Pip
Posts: 58
Joined: October 24th, 2017, 3:29 pm
Has thanked: 2 times
Been thanked: 55 times

Re: Share Prices in Excel - NEW update

#199618

Postby eventide » February 7th, 2019, 3:08 pm

wissamzeenni wrote:Hi, Great add-in, using it on a daily basis ! Thanks for all the efforts.

1 feature that I think is missing (or I could not figure it out yet!) is grabbing a share price for a particular date as an extra argument... For example =getdata(BOO.L,"close", "03/05/2017")

Not sure if this is feasible or already available....



Forgot about this, its easy to extract now I found the right api: the yahoo v8 api spits out a daily price history or monthly or whatever in handy json format

eg:

https://query1.finance.yahoo.com/v8/fin ... nterval=1d

so you should be able to write a function for what you need with that (Date array is in unix epoch format btw). There are good VBA json parsers available these days.

**Caution** it is only as good as the yahoo data, and sometimes that is sh*te for historic stuff

JamesMuenchen
Lemon Slice
Posts: 305
Joined: November 4th, 2016, 9:05 pm
Has thanked: 33 times
Been thanked: 37 times

Re: Share Prices in Excel - NEW update

#206410

Postby JamesMuenchen » March 8th, 2019, 10:26 am

Hi Eventide

Thanks very much for your great Add-in. It's awesome.

Sadly, I have some stocks that give a :tick_err: and not sure how to fix it

This one works
https://query1.finance.yahoo.com/v7/fin ... ymbols=AYX
This one doesn't
https://query1.finance.yahoo.com/v7/fin ... mbols=TWLO

The data looks a bit different but both have a "regularMarketPrice" field which I guess is what you use for the "last" price

Would it be possible to share the password or give an example how the code parses out the data?

Also does anyone know if there are "tickers" we can use to get exchange rates?
EDIT … just found EURUSD=X EURGBP=X, etc



thanks again
James

JamesMuenchen
Lemon Slice
Posts: 305
Joined: November 4th, 2016, 9:05 pm
Has thanked: 33 times
Been thanked: 37 times

Re: Share Prices in Excel - NEW update

#206416

Postby JamesMuenchen » March 8th, 2019, 10:53 am

JamesMuenchen wrote:Sadly, I have some stocks that give a :tick_err: and not sure how to fix it

OK, I closed and reopened EXCEL and the ones that had errors are working now. Everything works great!

Anyway thanks again, Eventide, for a great tool

PrefInvestor
Lemon Pip
Posts: 67
Joined: February 9th, 2019, 8:24 am
Has thanked: 2 times
Been thanked: 21 times

getdata addin & data fidelity

#207717

Postby PrefInvestor » March 14th, 2019, 5:24 pm

Hi All, Well I can certainly see that the getdata addin is a nice idea given its ease of use and different types of data that it can provide. But then again I note that there have been no new posts to this topic since March 2018......?

My personal experience with using data obtained automatically from various web sources can be summarised as follows:-
a) Data quality is really important. Any source probably can’t ever be 100% correct, but if it cant be relied on to be sort of 95% accurate then using it all may not a good idea. Garbage in garbage out as they say. And some of yahoos data items seem to be questionable particularly for UK stocks given some of the posts made here.
b) I think that you need to have a mechanism built into your spreadsheet that enables you to manually capture any data items that might be missing (or which you can see are wrong) and allow you to temporarily override them with a correct value. Hopefully you will only need to use this mechanism in a small number of cases but not having such a mechanism will likely be a problem. Though this is an issue for the getdata user rather than the addin itself.
c) Time delays inherent in the data can also be an issue (though perhaps not for HYPers who may only update their spreadsheets infrequently ?). Personally I much prefer using a source that provides real-time prices when the market is open. Or at worst is subject to a 15 minute delay. Some sources only provide end of day values, this might be OK for some people – but I would not want to go with that option. Confess I’m not sure what the Yahoo API does in this respect.
d) You need a pricing source that can provide data for ideally all of the investments that you hold (or might hold). Having a few missing items that need to be entered manually is OK, but if you have a lot of items not covered then doing a portfolio update would be a painful and time consuming process.
e) I have personally found obtaining accurate information on upcoming dividends quite difficult for my investments. I use http://www.dividenddata.co.uk but because I hold preference shares, investment trusts and ETFs and only a small selection of single stocks I find that this only meets a small fraction of my needs. I certainly could not rely on it to automatically populate ALL of my dividend data. Instead I manually maintain the dividend data for EVERY investment that I own or might own (about 100 investments) and display what http://www.dividenddata.co.uk contains about these in separate fields so that I can correct my data where necessary. This may sound like a massive undertaking but it isn’t really.

Anyway the getdata addin is clearly a tool that some in the community are finding very useful, so I can only commend the work that has been done.

ATB

Pref


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 3 guests