Retreive Data from Webpage

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​
 

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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
 

ac6murray

New Member
Joined
Jan 31, 2012
Messages
6
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>
?
 

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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
 

ac6murray

New Member
Joined
Jan 31, 2012
Messages
6
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
 

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
"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
 

Forum statistics

Threads
1,081,415
Messages
5,358,533
Members
400,502
Latest member
price83

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top