Automatic website import to QB field

  • 0
  • 2
  • Question
  • Updated 2 months ago
  • In Progress
We are trying to create a field/fields that will pull rates from www.snaprates.com into Quickbase that can be automatically update. Any help on this would be great. Thank you.  


Photo of Andrew Hagaman

Andrew Hagaman

  • 90 Points 75 badge 2x thumb

Posted 2 months ago

  • 0
  • 2
Many solutions can be there, depending on what that website provides, in terms of RSS feeds, json output, XML outputs etc. But without all the information, one solution that I can think of is to use Selenium automation to create a bot that could go to that website, copy selected portions of that website, and import that data into your qb tables, just as if a human being is doing it.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 28,004 Points 20k badge 2x thumb
There are a lot of reasons you can't do this:

(1) snaprates.com does not support https so you would get "mixed content" error.

(2) snaprates.com does not support cross domain requests.

(3) snaprates.com may or may not allow you to scrape their data

But this is QuickBase and nothing is impossible!

It has been years since I have done this but you can still use my old pal YQL (Yahoo Query Language) to fetch the data through their servers and return it to QuickBase (thus circumventing the same origin policy your browser enforces).

Here is proof:



Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=678

To repeat this result just copy the code in the above pastie and paste it into the console.

The key thing to note is that despite QuickBase and SnapRates being hosted on separate domains QuickBase can in fact access the SnapRates data by going through the YQL service. Note the dialog displaying the SnapRates data is being displayed within QuickBase.

You didn't say what data you wanted to extract so I just grabbed all the table data. It is trivial to extract specific data from the table data.




(Edited)
Photo of Andrew Hagaman

Andrew Hagaman

  • 90 Points 75 badge 2x thumb
Dan, 

That is great help. Thank you very much. To take it a step further for example I am trying take the 10yr treasury and directly put it into a field on QB so that I can do calculations with. Any idea on how to do that? 
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 28,004 Points 20k badge 2x thumb
YQL uses xpath selectors (not CSS selectors) to index into the html and select nodes. So if that was the only scrap of data you wanted to grab, you could create a pinpoint xpath selector to grab the value in the first table, fourth row, second column:

var site = "http://www.snaprates.com/index.php";
var yql = `
  SELECT *
  FROM htmlstring
  WHERE url='${site}' AND xpath='//section[@id="family1"]//table/tbody/tr[4]/td[2]'
  `;
var resturl = "https://query.yahooapis.com/v1/public/yql?q="; +
  encodeURIComponent(yql) + 
  "&format=json" +
  "&env=store://datatables.org/alltableswithkeys";
$.get(resturl, function(data) {
  //console.log(JSON.stringify(data.query.results.result, null, "  "))
  $("<div id=QBU_dialog>").html(data.query.results.result).dialog();
});

This will result in the following dialog:


To get this value into a table you stuff the value 
    data.query.results.result
into a field of the open form or call the API and place it where you want.

This is a very general procedure that can be used to extract data from just about any web page.
(Edited)
Photo of Eric Fixler

Eric Fixler

  • 82 Points 75 badge 2x thumb
Dan: Would the "stuff" action be via a formula field? I get an error when i run the code.



I am new to writing the API call function for edit record getting data from an outside source into the form.. Thank you,

Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 28,004 Points 20k badge 2x thumb
Apparently there is an extra semicolon in this code:
var resturl = "https://query.yahooapis.com/v1/public/yql?q="; +
  encodeURIComponent(yql) + 
  "&format=json" +
  "&env=store://datatables.org/alltableswithkeys";
Remove it and you should be good:
var resturl = "https://query.yahooapis.com/v1/public/yql?q=" +
  encodeURIComponent(yql) + 
  "&format=json" +
  "&env=store://datatables.org/alltableswithkeys";

(Edited)