You should look into "Power Query" (aka "Get & Transform" in Excel 2016): https://support.office.com/en-us/article/introduction-to-microsoft-power-query-for-excel-6e92e2f4-2079-4e1f-bad5-89f6269cd605

It allows you to get data from a number of sources including Web API's and pass in authentication information etc... You can extract as much or as little info from the returned data and do all sorts of transformations/extractions etc...