• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How can I Automate Data Retrieval from Web Page?

EXCELlentBacon

EXCELlentBacon

New Member
I maintain a number of dashboards at my company, and they require frequent refresh (some daily, some weekly). One source of data is an internal website where I must click through a number of HTML links to locate the appropriate report, then click a few buttons to run the report, and finally use radio buttons and dropdown menus to customize the export format, before exporting to a text file.

Is there a way to automate this repetitive task without installing Selenium or any other software? (company has a no-install policy)
 
That will depend on how your internal web sites are set up.

Check with your web/database admin.

1. If you are able to query result directly from back-end database. If able, this will be the quickest and most flexible method.

2. If the web service and/or whatever the package used to produce the web site has API that you can call on. REST/SOAP (preferably REST). If it uses proprietary software (such as Customer Relation Manager etc), you may need to contact the developer/vendor for the documentation.

Neither will require any additional software and can be done solely based on what's available to Excel (VBA, MS Query, PowerQuery etc).

EDIT: Some API requires use of CURL. Unless you can connect to server hosting the web page via console (if it's Lynux based machine), you may need the library and executable for CURL.
 
Thanks for the quick reply!

1. I am fairly certain company policy prohibits direct DB query (GROAN!). At prior companies this was my bread and butter. Hence, my frustration having to manually update dashboards.

2. How do I determine this? And what is my next step when I have my answer?
 
2. Ask whomever is maintaining your company web site.

Chances are web site is itself querying Database to spit out the report. See if you can leverage web site API automation to spit out report on schedule (I assume only parameter that change is time parameter). This can be done in many ways and will depend on your company's set up.

Best to work with IT/Network Admin and DB admins to get it working. They should be able to easily set up "csv" export of report to run on schedule and use SMTP to send email or use script to save to network drive.

You may need to get your boss involved to get other departments to work with you. ;)
 
You must log in or register to reply here.
Back
Top