Copy value from a internet page

Topic

New Reply

Hi,

Greetings!

I want to copy the value (current market value of a share) from an internet page and paste in an excel cell.

Whenever I open the excel file, this value should be replaced with the most current value from the internet page.

For example, in the attached file, I want the value in cell E2 to be copied from the website mentioned in cell G2 (as 572.85).

What is the most easiest way for the above? I searched the forum before posting here, but was quite confused with few of the threads (sorry I am not an expert in excel).

Thanks in advance.

BR,
Fahim

Reply | Quote

Replies

Fahim,

Welcome to the Lounge as a new poster.

The only way I know to do what you want is via the Data tab — From Web:

42467-WebData

You enter the web address then click the Go button to the right.
This will bring up your page and you can then select the appropriate yellow/black arrow for the data you want. In this case you have to select the one in the upper left corner for the entire page. Then click Import.

Select the location, I’d suggest New Worksheet A1.

You can then find the Current price and reference that cell on your main sheet.

You will need to refresh the page every time you load the workbook this can be done with a auto_open macro:

Code:

Option Explicit

Sub Auto_Open()

'*** Refresh Stock Quote Query ***

    Application.ScreenUpdating = False
    ActiveWorkbook.RefreshAll

End Sub    'Update_Quotes()

Of course this can get out of hand if you have a lot of individual stocks/mutual funds. What I use is ighome.com to setup an easily to import page.
42468-stocks
On this website I get a yellow arrow for just that section so it imports a lot less data and I get all my stocks on one page.

Your worksheet adjusted: 42469-Share_Value_Test

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

You could lash out on an Excel add-in ($15).
http://spearian.com/products/spearian-for-excel

Or go the free route.
https://code.google.com/p/finansu/

cheers, Paul

Reply | Quote

mdfahiem,

Here is some code that will load your website, scrape the value of the current stock, then place the value in cell E2. Run the code by clicking the button on the worksheet. Be patient as the website loads into memory.

You will need to reference 2 additional libraries:
Microsoft Internet Controls
Microsoft HTML Object Library

Let me know if you need instructions on how to do that, it is really quite simple. Let me know If I scraped the wrong value, it was an educated guess since the values have changed since your posting.

HTH,
Maud

Place in a standard module:

Code:

Sub GetTraderPrice()
[COLOR=”#008000″]’——————————–
‘DECLARE AND SET VARIABLES[/COLOR]
Dim IExplore As InternetExplorer
Dim html As HTMLDocument
Dim TraderPrice As IHTMLElement
Set IExplore = New InternetExplorer
[COLOR=”#008000″]’——————————–
‘LOAD WEBSITE[/COLOR]
Status.Show
IExplore.Visible = False
IExplore.Navigate “http://economictimes.indiatimes.com/wipro-ltd/stocks/companyid-12799.cms”
Do
    DoEvents
Loop Until IExplore.ReadyState = 4
[COLOR=”#008000″]’——————————–
‘RETRIEVE VALUE[/COLOR]
Status.Label1.Caption = “Retrieving Value”
Set html = IExplore.Document
Set TraderPrice = html.getElementById(“nseTradeprice”)
Range(“E2″) = TraderPrice.outerText
[COLOR=”#008000”]’——————————–
‘CLEANUP[/COLOR]
Unload Status
Set IExplore = Nothing
Set html = Nothing
End Sub

Reply | Quote

Thank you RetiredGeek, Paul and Maud for your guidance.

I tried all the options and found Maud’s as the one which satisfies my requirement.

Maud please help me for the below:
1. I don’t know what Microsoft Internet Controls & Microsoft HTML Object Library means and where I can use them.
2. Please send me the steps on how to do this (I need them to add more shares in excel).

Thanks again to all.

BR,
Fahim

Reply | Quote

Thanks all again.

I was able to copy the macro and paste and make the required changes. It’s working well if I have 2 to 5 shares, hope it will work well if I add more shares.

BR,

Reply | Quote

mdfahiem,

I take it that you were able to add the 2 vba references needed to run the code in your project? If you need to add to your project instead of working out of the revised workbook I provided, then let me know and I will give you the simple steps to do so.

The code will only work for one stock value. If you want to collect multiple values:
1. Extract multiple Stock values on the same web page– you duplicate the following lines for each additional stock while changing the variable name, looking up the element ID for the field in the source code, and changing it in the line code.

Code:

Dim TraderPrice As IHTMLElement
‘….
Set TraderPrice = html.getElementById(“nseTradeprice”)
Range(“E2”) = TraderPrice.outerText

2. Extract Stocks from multiple pages– you will need to loop through the code and insert new URL (array variable) with each loop. You will also need to find the element ID for each value field and inset it in the code line.

Please let me know which scenario or a combination of both that applies.

Maud

Reply | Quote

mdfahiem,

I take it that you were able to add the 2 vba references needed to run the code in your project? If you need to add to your project instead of working out of the revised workbook I provided, then let me know and I will give you the simple steps to do so.

The code will only work for one stock value. If you want to collect multiple values:
1. Extract multiple Stock values on the same web page– you duplicate the following lines for each additional stock while changing the variable name, looking up the element ID for the field in the source code, and changing it in the line code.

Code:

Dim TraderPrice As IHTMLElement
‘….
Set TraderPrice = html.getElementById(“nseTradeprice”)
Range(“E2”) = TraderPrice.outerText

2. Extract Stocks from multiple pages– you will need to loop through the code and insert new URL (array variable) with each loop. You will also need to find the element ID for each value field and inset it in the code line.

Please let me know which scenario or a combination of both that applies.

Maud

Hi Maud,

How are you doing?

I have copied the vba (sent by you) in my original file and whenever I hit the update button, another excel file is opening (Share_Value_Test_Revised.xlsm). I don’t want this file to open.

Right now, I copied the button and editing the vba as per my requirement, how to create a new button – please explain step wise.

I would like to know the steps required for the option 2 (Extract Stocks from multiple pages).

Many thanks for your kind guidance.

BR,
Fahim

Reply | Quote

Fahim,

When you copied the button, you also copied the call to the macro which is “Share_Value_Test_Revised.xlsm!GetTraderPrice”. So, when you click on the button, it is looking for a macro located in my revised spreadsheet

42558-Fahim1

Right click on the button > Assign Macro… > Select GetTraderPrice > OK. The button will now point to the macro in your workbook and not the one in mine.

Do you have the URL links for your other stocks? I will help you with the code to get those values.

Maud

Reply | Quote

Hi again,

I am trying to create a new macro as below:
1. Insert a image as Rectangle.
2. Assign the Macro (macro is copied from your previous post, editing done as per my requirement).
3. Click on the rectangle.
4. Message is shown as “Compile error: User-defined type not defined”
5. If I click ok, then it is highlighting “Dim IExplore As InternetExplorer”

Please guide me how to create a new macro.

Thanks again.

BR,
Fahim

Reply | Quote

You will need to reference 2 additional libraries:
Microsoft Internet Controls
Microsoft HTML Object Library

The error is caused by not having the above libraries referenced. If you need the simple instructions on how to add the references, please let me know or google it. I would suggest to post the modified macro so that can review and head off and unexpected behavior.

Maud

Reply | Quote

The error is caused by not having the above libraries referenced. If you need the simple instructions on how to add the references, please let me know or google it. I would suggest to post the modified macro so that can review and head off and unexpected behavior.

Maud

Could you please explain the instructions?

Thanks a lot for your kind support.

BR,
Fahim

Reply | Quote

Fahim,

1. From the excel spreadsheet, press Alt-F11 to open the VB Editor.
2. Click on Tools > References…

42637-fahiem1

3. There will be some libraries already checked. Scroll down and place a check mark next to Microsoft Internet Controls and Microsoft HTML Object Library.

42638-fahiem2

4. Click OK > Close the VB Editor (red “X”) > Save

The code should now run

HTH,
Maud

Reply | Quote

Hi Maud,

Greetings!

I am facing problem, when I start adding more shares. Please find the attached file. I have 4 shares, for first 2 shares, macros are working well. For last 2 shares, it is showing error as below:
“Cannot run the macro ‘Share_Value.xlsm!GetTradePrice’. The macro may not be available in this workbook or all macros may be disabled.”

Also is it possible to have one box in excel file which will automatically update all the values, instead of having 4 different boxes (one box for each share)?

I have noticed that whenever I open this excel file, my laptop will performance drops and it behaves very slow. Is there any specific reason for this?

Thanks in advance.

BR,
Fahim

Reply | Quote

“Cannot run the macro ‘Share_Value.xlsm!GetTradePrice’. The macro may not be available in this workbook or all macros may be disabled.”

Fahiem,

Nice job amending the code.

The reason you are getting the message for the last two stocks is because ‘Share_Value.xlsm!GetTradePrice’ macro does not exist. You need to set assign the Morepen button to the ‘GetTradePriceMor’ macro and the SPYL button to the ‘GetTradePriceSPYL’ macro.

To have one button run all the stocks you can:
1. (Easiest) Create a macro that the one button points to. In the macro, list the four subroutines. When the control macro runs, it will run the 4 macros successively.
2. Have one code called by one button which loops through the cells F2 through F5 and fins the values.

HTH,
Maud

Reply | Quote

To have one button run all the stocks you can:
1. (Easiest) Create a macro that the one button points to. In the macro, list the four subroutines. When the control macro runs, it will run the 4 macros successively.
2. Have one code called by one button which loops through the cells F2 through F5 and fins the values.

Hi Maud,

Thanks again.

Please explain me step wise how to create one button to run all macros. I mean what must be the code written in this one button.

BR,
Fahim

Reply | Quote

Fahiem,

Create a controller routine like this:

Code:

Public Sub Controller()
    GetTraderPriceWI
    GetTraderPriceSAIL
    GetTraderPriceMOR
    GetTraderPriceSPYL
End Sub

Have the button run the Controller sub and the Controller will run all 4 of your subroutines.

HTH,
Maud

Reply | Quote

hello everyone

i hope this might help:

in E2007, i do Data>Existing connections>MSN MoneyCentral Investor Stock Quotes

then choose destination cell for the data, and when asked for the tickers (symbols) you’re interested, enter them.

For this particular example, i entered JNJ, IBM, APPL and marked box to refresh for future refrences, then hit OK.

i’m attaching what i got

br.

Reply | Quote