I experimented with Power Query at work and now use it as a...

  1. 1,118 Posts.
    I experimented with Power Query at work and now use it as a means to have a 'distributed database of spreadsheets' for Project Management.

    For the AFR URL (or for any URL for that matter), the trick is to build as much as we can into the URL using their tools. I searched for 'ASX announcement search' and landed on their ASX announcement search page (http://www.copyright link/asx_announcements_search?openSearch=true). From there, I ticked the boxes I wanted (quarterlies), the date range (last 6 months) and the search terms (acquisition). Since the search didn't allow for logical operators, I simply then edited the resultCount variable so that it will display all results in one page. Once this is done, we can use Power Query to grab that HTML table and turn it into an Excel table on the fly.

    http://www.copyright link/asx_announcements_search?submit=true&q=acquisition&code=&marketSensitive=false&sortBy=DATE_LATEST_FIRST&asxCategories=Quarterly Activities Report:First Quarter Activities Report:Second Quarter Activities Report:Third Quarter Activities Report:Fourth Quarter Activities Report:Quarterly Activities Report - Other:&anzicCode=04000:04001:04002:04003:04004:04005:&gicsCode=&dateRange=last_6_months&specificDateFrom=&specificDateTo=&resultCount=999999&firstResultItem=0#
    Last edited by hibikidan333: 04/12/15
 
arrow-down-2 Created with Sketch. arrow-down-2 Created with Sketch.