Retreive Data from Webpage

A

ac6murray

New Member
Joined
Jan 31, 2012
Messages
6
Hi All,

I am trying to retreive share prices from the following page http://www.asx.com.au/asx/markets/pr...Codes&asxCodes= AMP
I can navigate several pages and searches to get there but I can't seem to get the specific piece of data I want to extract. I want to extract the last share price, the html code for this is as follows:
<tdclass="last">4.270 </td>

Here is the vb script that is causing me problems (and the Dim settings for the entire script, I have not inculded the entire script as the other parts are working fine), I have tried it a couple of ways:

Dim clip As DataObject
Dim Element As Object
Dim btnInput As Object ' MSHTML.HTMLInputElement
Dim ElementCol As Object ' MSHTML.IHTMLElementCollection
Dim Link As Object ' MSHTML.HTMLAnchorElement
Dim ieTable As Variant
Dim strCountBody As String
Dim lStartPos As Long
Dim lEndPos As Long
Dim TextIWant As String
Dim i As Long
Dim ie As Object

Option 1:

With .Document.Forms(2)

For i = 0 To .getElementsByTagName("td").Length - 1
Set ieTable = .getElementsByTagName("td").Item(i)
TextIWant = ieTable.getAttribute("innerText")
If TextIWant <> "" Then
If InStr(1, TextIWant, "last") > 0 Then
Range("C" & ActiveCell.Row).Value = TextIWant
Exit For
End If
End If
Next

Option 2:
With .Document.Forms(2)

Set ieTable = .All.Item("last")
Set clip = New DataObject
clip.SetText "<html>" & ieTable.outerHTML & "</html>"
clip.PutInClipboard
Range("C" & ActiveCell.Row).Select
Range("C" & ActiveCell.Row).PasteSpecial

Thanks, your help would be much appreciated. I'm sure I have made some rookie error as I have done lots of website navigation via excel (and form filling), but never retrieval.

Cheers​
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
is not the webpage slightly changed
the correct page appears to be
http://www.asx.com.au/asx/markets/priceLookup.do?by=asxCodes&asxCodes=+amp

that is = amp it should be =+amp

any way try this macro (the macro may be pedestrian bult it gets what you want it is OK

Code: 
Sub Macro1()
Dim cfind As Range
    
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.asx.com.au/asx/markets/priceLookup.do?by=asxCodes&asxCodes=+amp" _
        , Destination:=Range("A1"))
        .Name = "priceLookup.do?by=asxCodes&asxCodes=+amp"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    With ActiveSheet
    Set cfind = .Columns("A:A").Find(what:="code", lookat:=xlWhole)
    Range(cfind.Offset(-1, 0), Range("a1")).EntireRow.Delete
    Range(cfind.Offset(2, 0), Cells(Rows.Count, "A")).EntireRow.Delete
    End With
End Sub
 
Upvote 0
Hi Venkat,

Thanks for your reply however I am only after the last share price figure from the webpage and I want it to be "pasted" in a specific excel field.

Also as multiple share codes will be looked up I don't want to have to specify a "tag" for a specific share tag in the script (ie. priceLookup.do?by=asxCodes&asxCodes=+amp) as this would limit the script to only using it with one particular share AMP.

Is there anyway to grab the data for the this particular class <tdclass="last">4.270 </< font>td>
?
 
Upvote 0
this can be done even here. you have to have the list of stocks and loop though each of them in the macro. you can try at writing the macro based on the macro given.

what does it matter if you get more data open high low close etc. use a vlookup to get whichever you want in another sheet.



there are many other references in the Google search

if you are NOT particular in getting data only from "asx" then you can try
smf addin

http://finance.dir.groups.yahoo.com/group/smf_addin/?v=1&t=directory&ch=web&pub=groups&sec=dir&slk=2


whrere you can have the list of stocks and get the prices ontime.. this addin gets data from yahoo

another way is to creae a portfolio in yahoo finance and having a customized view containing only the prices you want and download the data into excel


think about these and see various options
 
Upvote 0
Unfortunately I cannot use another website in this case due to what the entire macro is used for the user will only accept price data directly from the ASX :mad:.

I understand what you are saying about the vlookup, but it won't work in this case as the spreadsheet the data needs to appear in will only accept the last share price.

I appreciate your help and will try and tweak parts of you macro to get it to suit my needs, unless someone has a better idea ;)

Thanks
 
Upvote 0
"spreadsheet the data needs to appear in will only accept the last share price."

any excel spreadsheet accept vlookup.

in the file which accepts your last price(let us call it sheet1),I assume , is a excel spradsheet. insert another sheet (call in sheets2) in the file and in that sheet download using my macro. and then sheet1 in the cell you want to download last price use vlooiup.


perhaps I have not seen your file. will it be possible to p ost the file with some random data for security reasons

any how do some experiments
best luch
 
Upvote 0
You must log in or register to reply here.

Similar threads

netspeedz
  • Solved
Extracting Data From Kitco.com [Using VBA]
Replies
6
Views
399
netspeedz
netspeedz
C
  • Question
Scrape data from password protected site without using Internet Explorer
Replies
6
Views
5K
Dan_W
D
T
  • Question
Need help with vba and Html dropdown.
Replies
0
Views
544
tburg73
T
N
  • Question
Using VBA to copy data from Webpage table to excel
Replies
2
Views
6K
New_Grasshopper
N
W
  • Question
VBA IE Automation Logon, Navigate multiple buttons and then scrape data back into excel
Replies
0
Views
2K
wid2001
W

Forum statistics

Threads
1,214,403
Messages
6,119,309
Members
448,886
Latest member
GBCTeacher

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top