PDA

View Full Version : Pulling Data into Excel from IE Javascript table?



igotgame
03-28-2009, 11:53 AM
I am attempting to pull data from a Javascript table into a Excel spreadsheet. Web Query won't work since this isn't an HTML table.

Here is the page source:

<script language="JavaScript" type="text/javascript" src="/js/open/jquery.js"></script>
<script type="text/javascript">

function shortenDate(full) {
var month = full.substring(0,3);
var rest = full.substring(full.indexOf(' '));
var temp = month + ". " + rest;
return temp;
}

$(function(){
$.get("/cgi-bin/pmms.cgi?display=release",function(xml){
$('item',xml).each(function(i) {
var ending_date = $(this).find('ending').text();
var feed30 = $(this).find('#US #30YRFRM').find('rate').text();
var fees30 = $(this).find('#US #30YRFRM').find('fees').text();
var feed15 = $(this).find('#US #15YRFRM').find('rate').text();
var fees15 = $(this).find('#US #15YRFRM').find('fees').text();
var nextUpdate = $(this).find('nextUpdate').text();
var currentReleaseURL = $(this).find('link').text();
$('#date').append('<strong>' + shortenDate(ending_date) + '</strong>');
$('#avg30').append(feed30+'%');
$('#avg15').append(feed15+'%');
$('#fees30').append(fees30);
$('#fees15').append(fees15);
$('#nextUpdate').append(nextUpdate);
$('a#currentReleaseURL').attr('href', currentReleaseURL);
});
});
});
</script>


How can I pull one of the variables into my spreadsheet? I need that feed30 variable.

This is on: http://www.freddiemac.com webpage if that helps.

The Value I need is the 4.85% from the small table on the left under 30 Year.

Thanks for the help!

stanl
03-29-2009, 03:30 AM
hmmm... interesting, and challenging. I tried

http://www.freddiemac.com/pmms/release.html

which has several tables but the values are calculated on the fly by jscript. I think the solution is to persist the page source to a local file and insert additional jscript code to obtain the values. I'll get back to you on that. Stan

igotgame
03-29-2009, 08:30 AM
hmmm... interesting, and challenging. I tried

http://www.freddiemac.com/pmms/release.html

which has several tables but the values are calculated on the fly by jscript. I think the solution is to persist the page source to a local file and insert additional jscript code to obtain the values. I'll get back to you on that. Stan
Thanks Stan...

Yea it is challenging...I actually tried this last night.

Set htmlColl = htmlDoc.getElementsByTagName("TD")
For Each htmlInput In htmlColl
If htmlInput.ID = "avg30" Then
WorksBooks(1).Worksheets(1).Cells(1, 1) = htmlInput.Value

Just to see if I could actually get it to find the variable....it did, but you can't do htmlInput.Value of avg30 because avg30 is in some javascript.

stanl
03-29-2009, 08:38 AM
I got this to work. It appears they hide the value by inserting a "&nbsp;" as part of the return value.

Try something like (it worked for me)


Sub getrate()
Dim oIE, var, cURL
cURL = "http://www.freddiemac.com/pmms/release.html"
Set oIE = CreateObject("InternetExplorer.Application")
oIE.Visible = 1
oIE.Navigate cURL
While oIE.Busy = True
DoEvents
Wend
var = oIE.Document.Body.all.Item("US30FRMrate").InnerHTML
var = Replace(var, "& nbsp;", "") 'Delete space in "& nbsp;"
result = MsgBox(var, 0, "US30FRMrate")

End Sub


Stan

stanl
03-29-2009, 08:43 AM
AAARRRGGHH!

The 2nd parm in the line var = Replace(var, " ", "") should be "&nbsp;"

mdmackillop
03-29-2009, 09:42 AM
AAARRRGGHH!
I see what you mean!

igotgame
03-29-2009, 09:42 AM
Stan

I got this:



Sub Freddie_Rate()

Dim oIE, var, cURL
cURL = "http://www.freddiemac.com/pmms/release.html"
Set oIE = CreateObject("InternetExplorer.Application")
oIE.Visible = 1
oIE.Navigate cURL
While oIE.Busy = True
DoEvents
Wend
var = oIE.Document.body.all.Item("US30FRMrate").innerHTML
var = Replace(var, "&nbsp;", "")
Result = MsgBox(var, 0, "US30FRMrate")



End Sub


But I keep getting this:

Object Variable or With Block Variable not set on the var = oIE.Document.body.all.item line

I have a few references on but are there any in particular I need on?

mdmackillop
03-29-2009, 09:56 AM
I get that as well, but I got the result in stepping through. Looks like you need a different pause routine.

igotgame
03-29-2009, 10:01 AM
Thanks for that...I got it now...I have the pimp of pause routines

It will even wait for frames to load. That way if you have certain frames loading faster than others...pause still works.


With oIE
Do While .Busy: DoEvents: Loop
Do While .ReadyState <> 4: DoEvents: Loop
Do Until .Busy = False
Loop
While oIE.ReadyState <> READYSTATE_COMPLETE
DoEvents
Wend
Dim objHTML As HTMLDocument
Set objHTML = .Document
While objHTML.ReadyState <> "complete"
DoEvents
Wend
End With


Thanks for all your help guys!

I got it all working now like I want it!

stanl
03-29-2009, 12:00 PM
You are most welcome; sorry about the confusion, I actually wrote the script in Winbatch, then did my best to translate to VBA

original code

cURL="http://www.freddiemac.com/pmms/release.html"
oIE = CreateObject("InternetExplorer.Application")
oIE.Visible=1
oIE.Navigate(cURL)
If ! ieready(30,"Opening ":cURL) Then end()
var = oIE.Document.Body.all.Item("US30FRMrate").InnerHTML
var = StrReplace(var,"nbsp;","")
Message("","US30FRMrate":@CRLF:var)


ieready() is a udf I wrote which checks for both busy and readystate, the first parm is the amount of seconds to attempt before giving up. Funny thing is the VBA code worked first time also... Internet must have got busy.

Depressing looking at those numbers, we had our first mortgage near the end of the Reagan presidency, you were lucky if you could get an ARM at 9.5% and fixed were around 13% :motz2: Stan

igotgame
03-29-2009, 01:53 PM
Thanks again.

I write loan modification programs for a rather large mortgage company and am currently working on the HAMP plan (obama's plan) and that 1 variable was the only one giving me trouble....stupid javascript sometimes makes my life difficult...heh

So you just saved me a couple of hours tomorrow morning...thanks again.