Excel Web Query - extract single column of info only

[Tian1]

Hi

I’ve just recently started using web queries in excel. I am, however, stuck on one particular place. The link I retrieve my query from is http://www.sharenet.co.za/v3/quickshare.php?scode=AGL
(If you want to view the link, copy paste it into the URL–field as a direct link won’t show you the desired data)

The actual text file used for the query has the following info:

http://www.sharenet.co.za/v3/quickshare.php?scode=AGL

Selection=10
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

When running this query you will find the following result (the numbers may vary):

Delayed Prices (11/01 16:35)
Sale 31370
Buy 31358
Sell 31388
Move -330
%Move -1.04%
Volume 2,178,504
Deals 3085
Today’s High 31818
Today’s Low 31350

It works perfectly but I only have the need to display the “sale” column and want to get rid of the others. Is there a way in which I can achieve this?

Also, what is the meaning of SingleBlockTextImport=False (and what happens if you set this to true? I can’t help but think that it might have something to do with what I am initially trying to achieve)

Thanks!

[Kyle123]

This is a slightly different approach, but will just return the value for a given label (sale, buy, sell etc), they are however case sensitive, so Sale would work where sale wouldn't. This will also work as a worksheet function.

Please Login or Register  to view this content.

[Tian1]

Thanks very much for the code! I hardly know anything about macros or worksheet functions but after this example I'm amped to learn!

Is there a way to alter the code so it can automatically look in a cell (let's say A1) and search for that particular share code on the website?
I've got a few cells that contain these 3-letter codes and want to automate the process.

Basically it looks like this:
A B
1 ASA
2 AGL
3 AMS

I just want the marco to automatically display the Sale-column value in B1

Thanks again for the help!!

[Kyle123]

Well you can use it as a worksheet formula so:

=GetValue(A1,"Sale")  

Where the code is in A1, then you can drag down

[Tian1]

Hi

Unfortunately, I have no experience in MVBasic so I tried editing the code but to no avail. Where should I insert the = GetValue(A1, "Sale")? I've tried replacing it with MsgBox GetValue("AGL", "Sale") but that obviously doesn't work.
I appreciate the help!

Function GetValue(sCode As String, sLabel As String) As Long

With CreateObject("MSXML2.XMLHTTP")
.Open "GET", "http://www.sharenet.co.za/v3/quickshare.php?scode=" & sCode
.send
Do
DoEvents
Loop Until .readyState = 4
GetValue = CLng(Val(Split(Split(.responseText, "<td class=""textCell"">" & sLabel & "</td>")(1), "<td class=""dataCell"" align=""right"">")(1)))
.abort
End With


End Function

Sub t()
=GetValue(A1,"Sale")
End Sub

[Kyle123]

Just in the worksheet. You don't need to edit the code, just use the above like you would any other function. Sum etc

[Tian1]

Thanks very much! Works perfectly!

[Tian1]

Hi

I posted a thread more than a year ago and since then I've been very happy using the macro-code you gave me. I stopped using it for a while and about a week ago when I tried to use it again, it only return a #VALUE error in the cell. Could it be that the URL changed or some of my security settings changed? The macro-code gives me an error "Subscript out of range".

Just a quick recap of what is supposed to happen: I simply want stock-prices returned from a website - http://www.sharenet.co.za/v3/sharesf...sname=&scode=z

OR an alternative URL: http://www.sharenet.co.za/v3/q_sharelookup.php

EXAMPLE:

A (Code) B (Price)

1 ANGSBE =GetValue(A1,"Sale")
2 GFISBO =GetValue(A2,"Sale")
.
.
.


The code you gave me is as follows:

Function GetValue(sCode As String, sLabel As String) As Long

With CreateObject("MSXML2.XMLHTTP")
.Open "GET", "http://www.sharenet.co.za/v3/quickshare.php?scode=" & sCode
.send
Do
DoEvents
Loop Until .readyState = 4
GetValue = CLng(Val(Split(Split(.responseText, "<td class=""textCell"">" & sLabel & "</td>")(1), "<td class=""dataCell"" align=""right"">")(1)))
.abort
End With


End Function

Sub t()
MsgBox GetValue("ASA", "Sale")
End Sub

It seems the error is in the GetValue = CLng line...

I hope you can help! Thanks!