Remove ads

Introducing the LemonFools Personal Finance Calculators

Share Prices in Excel - NEW update

Discussions regarding financial software
kiloran
Lemon Quarter
Posts: 1306
Joined: November 4th, 2016, 9:24 am
Has thanked: 210 times
Been thanked: 524 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: 81
Joined: November 7th, 2016, 9:33 am
Has thanked: 2 times
Been thanked: 25 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: 55
Joined: October 24th, 2017, 3:29 pm
Has thanked: 2 times
Been thanked: 52 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: 1306
Joined: November 4th, 2016, 9:24 am
Has thanked: 210 times
Been thanked: 524 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: 81
Joined: November 7th, 2016, 9:33 am
Has thanked: 2 times
Been thanked: 25 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: 81
Joined: November 7th, 2016, 9:33 am
Has thanked: 2 times
Been thanked: 25 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
2 Lemon pips
Posts: 230
Joined: November 4th, 2016, 10:15 am
Has thanked: 11 times
Been thanked: 61 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: 1306
Joined: November 4th, 2016, 9:24 am
Has thanked: 210 times
Been thanked: 524 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
2 Lemon pips
Posts: 230
Joined: November 4th, 2016, 10:15 am
Has thanked: 11 times
Been thanked: 61 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: 55
Joined: October 24th, 2017, 3:29 pm
Has thanked: 2 times
Been thanked: 52 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


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 1 guest