Importing Information from the Internet 1

[davidmo]

I have to design a database for mortgage brokers. A business requirement for the database is the ability to go out to the web and download the current rates (fixed or adjustable) for certain financial institutes on a daily basis. Then compare these new rates to the ones that the brokers had given previous clients to see if it is time to refinance.

The rates for the previous clients are located in Loan_Information table in the field Loan_Interest_Rate, also located in the table is the lender's name, Lender_Company_Name.

My first issue is how I can reach out to the web through Access and download in a table form the Lender's daily rate.

Thanks.

 

[SeeThru]

Try putting the keyword "Web Scaper" into google - I tried one in Access ages ago, but failed. I am now working on Visual Studio which can define web data sources, but have not tried that as yet.

 

[CautionMP]

davidmo,
This subject comes up a lot and I have yet to see a 'slick' way to go about it. Like [navy]SeeThru[/navy] I looked at third party solutions at one point and in my opinion they were over billed and hard to use, so ended up writing my own HTML handlers to parse information.

Depending on your skills here are a couple of thoughts:
[ol][li]Look at Web Queries in Excel. They appeared in Excel 2000 and in my experience got better in Excel 2003. They will allow you grab data contained in web page tables and display it in a worksheet that you can then link or import into Access.[/li]
[li]Look at using a Webbrowser control on a form in Access. This will let you navigate from web page to web page using Access and exposes the underlying source of the web page which can be parsed into a table structure (I have an example if you would like see it).[/li][/ol]

Either method will require a fair amount of coding, either to control Excel from Access so the process is button puch simple if there are multiple source web pages, or manually handling HTML source code to get meaningful record information.

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[davidmo]

SeeThru and CMP:
thanks for the info. will try excel first.

CMP, I would like to see the webbrowser piece. Much appreciated.

DMO

 

[CautionMP]

davidmo,
You asked for it. Before we start please note that this is only one way, not the only way, not the best way.

Here is roughly what it does:[ol]
[li]Opens a web page with data (from Bankrate.com in this example).[/li]
[li]When the button [tt]Command1[/tt] is clicked it does three things:[ol]
[li]Finds the 22nd table in the web page.[/li]
[li]Converts the data in the table to a bar delimited text file (so you can see the converted data).[/li]
[li]Imports the data from the bar delimited text file into the table [tt]tblBankrate_webpull[/tt][/li][/ol][/li]
[/ol]

Here are the steps to make it work.[ol]
[li]Create a new blank database.[/li]
[li]In the new database create a new form and create the following 2 controls:[ol]
[li]A button called [tt]Command1[/tt][/li]
[li]Insert an ActiveX Control of the type Microsoft Web Browser named [tt]axcWebBrowser[/tt][/li][/ol][/li]
[li]Navigate to the VBE pane for the new form a paste the following code into it:

Private Sub Form_Load()
Me.axcWebBrowser.Navigate "[URL unfurl="true"]http://www.bankrate.com/brm/rate/mtg_ratehome.asp?"[/URL] & _
                          "params=165000,CO,44&product=1&pType=f&refi=0&sort=3&points=6"
End Sub

Private Sub Command1_Click()
Dim colTables As Object
'Get the table with the data and output as a file
Set colTables = axcWebBrowser.Document.All.tags("TABLE")
CreateBarDelimitedFile colTables(22).innerHTML
Set colTables = Nothing
'Import the file
ImportData
End Sub

Sub CreateBarDelimitedFile(HTMLTable As String)
Dim blnCapture As Boolean, blnInTag As Boolean
Dim lngItem As Long
Dim intFile As Integer, intNestledTable As Integer
Dim strHTML As String, strOutput As String
intFile = FreeFile
Open CurrentProject.Path & "\Bankrate_webpull.txt" For Output As #intFile
'cycle trough the contents one character at a time
For lngItem = 1 To Len(HTMLTable)
  'Starting a tag so ingore the character until text ends
  If Mid(HTMLTable, lngItem, 1) = "<" Then
    blnInTag = True
  End If
  
  'They nestled a table in the one we want, keep track to know
  'when the current row ends
  If Mid(HTMLTable, lngItem, 6) = "<TABLE" Then
    intNestledTable = intNestledTable + 1
    lngItem = lngItem + 6
  End If

  'Current character is not in a tag so check to see if it
  'should be captured
  If Not blnInTag Then
    'Test to see if the current character begins a special character
    If Mid(HTMLTable, lngItem, 1) = "&" Then
      'Most* HTML special characters start with '&' and end ';' so
      'skip over them
      Do
        lngItem = lngItem + 1
      Loop Until Mid(HTMLTable, lngItem, 1) = ";"
    Else
      'it dosen't so capture the character
      strOutput = strOutput & Mid(HTMLTable, lngItem, 1)
    End If
  End If
  
  'we are at the end of a row so put in a delimiting character '|'
  If Mid(HTMLTable, lngItem, 3) = "<TD" Then
    strOutput = strOutput & "|"
    lngItem = lngItem + 3
  End If
  
  'Cycled through all the nestled tables and the row delimiter
  'has been reached so write to the output file
  If Mid(HTMLTable, lngItem, 3) = "<TR" And intNestledTable = 0 Then
    'to keep the output on the same line remove the line feeds
    strOutput = Replace(strOutput, vbCrLf, "")
    'Make sure there is data to write
    If Len(strOutput) <> 0 Then
      Print #intFile, strOutput
    End If
    'clear the output in preperation for the next line
    strOutput = ""
    lngItem = lngItem + 3
  End If
  
  'update the nested table pointer
  If Mid(HTMLTable, lngItem, 8) = "</TABLE>" Then
    intNestledTable = intNestledTable - 1
    lngItem = lngItem + 8
  End If
  
  'Probably reached the end of the tag
  If Mid(HTMLTable, lngItem, 1) = ">" Then
    blnInTag = False
  End If
Next lngItem
Close #intFile
End Sub

Sub ImportData()
On Error GoTo ImportData_Error
'This will load the data stored in the bar delimited text file
'CurrentProject.Path & "\Bankrate_webpull.txt" into table
'tblBankrate_webpull. Will create the table if it doesn't exist
Dim dbsCurrent As DAO.Database
Dim rstDestination As DAO.Recordset
Dim intFile As Integer
Dim stSQL As String
Dim strLine As String, strRecord() As String
intFile = FreeFile
Set dbsCurrent = CurrentDb
Set rstDestination = dbsCurrent.OpenRecordset("tblBankrate_webpull")
Open CurrentProject.Path & "\Bankrate_webpull.txt" For Input As #intFile
Do
  Line Input #intFile, strLine
  strRecord = Split(strLine, "|")
  If IsDate(strRecord(6)) Then
    With rstDestination
      .AddNew
      .Fields("Lender") = strRecord(3)
      .Fields("RateDate") = strRecord(6)
      .Fields("APR") = strRecord(7)
      .Fields("Discount") = Left(strRecord(8), InStr(strRecord(8), "/") - 1)
      .Fields("Points") = Mid(strRecord(8), InStr(strRecord(8), "/") + 1)
      .Fields("Rate") = strRecord(9)
      .Fields("Fees") = strRecord(10)
      .Fields("Lock") = strRecord(11)
      .Fields("Est_Payment") = strRecord(12)
      .Fields("Comments") = strRecord(13)
      .Update
    End With
  End If
Loop Until EOF(intFile)
Clean_Up:
Close #intFile
rstDestination.Close
Set rstDestination = Nothing
Set dbsCurrent = Nothing
Exit Sub
ImportData_Error:
Select Case Err.Number
  Case 3078
    'The Microsoft Jet database engine cannot find the input
    'table or query 'tblBankrate_webpull'.
    'Probably first run, create the table
    CreateTable
    Resume
  Case 3022
    rstDestination.CancelUpdate
    Resume Next
  Case Else
    Debug.Print Err.Number, Err.Description
    Err.Raise Err.Number
End Select
Resume Clean_Up
End Sub

Sub CreateTable()
'This is a run once procedure that will create a new table
'the first time the Button1 is clicked
Dim strSQL As String
strSQL = "CREATE TABLE tblBankrate_webpull (Lender char(50)," & _
         " RateDate DateTime," & _
         " APR double," & _
         " Discount double," & _
         " Points double, " & _
         " Rate double," & _
         " Fees currency," & _
         " Lock integer," & _
         " Est_Payment currency," & _
         " Comments char," & _
         " CONSTRAINT TableKeys PRIMARY KEY (Lender, RateDate));"
DoCmd.RunSQL strSQL
End Sub

[/li]
[li]Now in the property sheet make sure [tt][Event Procedure][/tt] is listed for the following:[ul]
[li]Form: On Load[/li]
[li]Command1: On Click[/li][/ul]
[/li]
[li]Save the form, switch to form view and click the button.[/li][/ol]

If you don't get any errors you can close the form and open [tt]tblBankrate_webpull[/tt] and see the data from the webpage.

Hopefully I did everything correct,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[davidmo]

CMP:
Thanks. Will try it and let you know.

DMo