Using Jscript based IE Add-On to export data from any website

[アーティクル]
07/23/2007

Often times we browse to a website which has lot of data in tabular format, or we login to secure website which displays data like financial transactions etc. in a neatly formated tabular format. Sometimes we have a need to export such data into another tool, if the website supports such an export good, otherwise we have to manually cut and paste the data into a text file, and painfully convert it into the desired format of the target tool. This process can be very lengthy, painful and error prone. We can use some kind of a script to try to automate some part of this process specially the post processing of the cut-and-pasted data to get into the desired format. But the final process is still not completely automated and has lots of manual steps involved.

What if we can easily write an add-on for the browser which can, not only extract the right set of data from a website we are browsing but can also easily convert it into any format we desire. In this blog post I will give step-by-step instruction on how to develop a Jscript based IE Add-On to achieve exactly such a solution.

In this example we will use an online stocks and mutual funds trading website www.icicidirect.com and write an IE add-on to extract trade transaction details from it, and then to export it in QIF format so that it can be imported into personal finance tools like Microsoft Money.

There are four major requirements for this add-on:

We should be able to invoke it from IE while browsing a web page and it should be allowed to access the contents of that web page.
It should be able to traverse the contents of the web page easily and locate the fields which are of interest.
It should be able to easily convert the data in the desired output format.
And finally it should be able to write the data at the desired location on user machine.

For the first requirement i.e. attaching it to IE, we will make use of the "Adding Entries to the Standard Context Menu" facility in IE. There are many different ways to extend functionality in IE but this one is one of the simpler ones and it satisfies our requirement perfectly. Only thing we need to do for this is to run the following command:

REG ADD "HKCU\Software\Microsoft\Internet Explorer\MenuExt\icicidirect2qif" /ve /d "file://C:\Program Files\EXTENDIE\icicidirect2qif.htm"

What this does is add registry entry for IE to start showing 'icicidirect2qif' as an item in the context menu which shows up anytime you right click in a web page. And when you click on that item IE will run the file "C:\Program Files\EXTENDIE\icicidirect2qif.htm" in context of that web page. The file icicidirect2qif.htm can contain Jscript code written inside the tags <SCRIPT>...</SCRIPT> e.g.

...

</script>

For the second requirement, when IE calls into icicidirect2qif.htm, the script code inside this page can use DOM element "external.menuArguments.document" to access any and all the contents of the web page currently being browsed in IE. In your Jscript code you can use one or more of the properties or methods available in IE to traverse through the 'document' object. Our target web page in icicidirect site had named <text> fields containing the transaction details like 'date', 'amount', 'mutual fund name', 'price' etc. so we have used the DOM call "getElementsByName" to locate and get data for the fields we are interested in, for example "var date = doc.getElementsByName("trans_date"+i)[0];" etc.

For the third requirement, Jscript has a number of useful "String" and "Number" APIs to work with the data we have extracted out of the web page and to convert it to any target format we are interested in, for example we have used code like:

        var units = doc.getElementsByName("Trans_Units"+i)[0].value;
        units = units.replace(/,/g, "");
        amount = new Number(amount); units = new Number(units);
        var price = amount/units;

to automatically calculate 'price' information for a transaction given the 'amount' and 'quantity' information extracted from the web page.

For the fourth and final requirement, Jscript has multiple ways to write data out, one of the simplest method is to use the 'document.write()' API to print output in the currently browsed web page itself. Alternatively you can also access the 'clipboard' object from Jscript and write output into the clipboard which can later be pasted into any application you want. We have used 'ActiveXObject("Scripting.FileSystemObject")' to write out our data into a file on user's machine as follows:

    var fso = new ActiveXObject("Scripting.FileSystemObject");
    var FileObject = fso.OpenTextFile("d:\\export.qif",8,true,0);
    FileObject.WriteLine("D"+date[1]+"/"+date[0]+"/"+date[2]);

In fact there is another ActiveXObject Shell.Application, which can be used in the end to automatically invoke Microsoft Money QIF import tool to start and load the QIF file we have just written out in our example we skipped this because we were writing out an 'investment' account which needs to be loaded into Money using 'File->Import->Restore Account' while the standard QIF import tool in Money works for the normal 'banking' account:

    var oShell = new ActiveXObject("Shell.Application");
    oShell.ShellExecute("d:\\export.qif");
    oShell = null;

Here is the code for the complete "icicidirect2qif.htm":

Comments

Anonymous
July 23, 2007
The comment has been removed
Anonymous
July 23, 2007
Very good article
Anonymous
August 30, 2007
Nice...
Anonymous
August 30, 2007
Cool.
Anonymous
January 19, 2008
Very good article
Anonymous
January 16, 2009
<a href= http://scpgiee.angelfire.com >bars in red bank nj</a> <a href= http://vlejlpy.angelfire.com >cattleman hats for kids</a> <a href= http://kroeouk.angelfire.com >crossland real estate</a> <a href= http://znioage.angelfire.com >s inn emporia virginia</a> <a href= http://qoqiapy.angelfire.com >aimee deeken</a>
Anonymous
September 08, 2009
How can I do the same for a website which has tabular data but not quite related to money as in icici example ? Can you please let me know for http://cns.miis.edu/pubs/other/sjm_cht.htm
Anonymous
July 08, 2012
Get Free Share Tips, Intraday Tips and Commodity Tips For Online Trading, Free Stock, MCX, NCDEX, NSE, BSE, Nifty, Forex Currency and Share Tips. E Trade Trading Accounts, Learn Commodity Trading, Forex Trading, Online Trading for Share Market Tips For One cup of coffee please visit : http://shareintradaytips.com

次の方法で共有

Using Jscript based IE Add-On to export data from any website

Comments

その他のリソース