Automatic website import to QB field
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.
Forum Discussion
- 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.
- 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. - 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?- 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 valuedata.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.
- 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,- Apparently there is an extra semicolon in this code:
var resturl = "https://query.yahooapis.com/v1/public/yql?q="; +
Remove it and you should be good:
encodeURIComponent(yql) +
"&format=json" +
"&env=store://datatables.org/alltableswithkeys";var resturl = "https://query.yahooapis.com/v1/public/yql?q=" +
encodeURIComponent(yql) +
"&format=json" +
"&env=store://datatables.org/alltableswithkeys";
