User blog:SlatBH/How to scrape page data into Excel (v16.30) on Mac

Okay after much reading and trying to use Excel's Power Query and other methods to bring data from a web page (i.e. pages from this wiki) into Excel, I finally found a solution.

First, Microsoft have stated that they intend to bring full functionality of Power Query (PQ) into the Mac version of Office 365. However and at time of writing, the current functionality is limited. See https://blog-insider.office.com/2019/05/30/you-asked-for-it-a-journey-to-power-query-in-excel-for-mac-starts-now/

So until PQ functionality is upgraded, the method is somewhat cumbersome as the Mac version of Excel does not include a 'Create New Web Query' option, only a 'Run Web Query' so one needs to create their own query.

To do this, create a text document and make sure the file name ends in the suffix 'iqy', for example, McLaren 720S Coupe.iqy. I recommend doing this in a text editor (I use TextWrangler), but it can be done in MS Word but be sure to save file as .txt and not .doc/.docx and save as MS DOS format. If you create this file on a Mac, you will probably have to rename the filename in Finder to remove the '.txt' that MS Word adds by default.

Once you have the text document, simply copy and paste the URL of the page that you wish to scrape/import and save the document in a convenient location.

Now, open a blank Excel document/sheet and place the cursor where you want the data to start (normally cell A1). From menu bar in Excel find the Data menu (not Data ribbon), select Data -> Get External Data -> Run Web Query... and then select the text file you created earlier and press the Get Data button. You should after a few seconds have the imported page. Note that you may need to format some of the cells to reveal the full contents. For example, I was only seeing times imported from WTTT Leaderboard to one decimal place before apply a custom format to these cells.

I hope this helps at least one person and I am happy to try to answer questions.