There was VBA code to deal with JSON data but now Excel can handle it directly. JSON is another data transfer format which comes as a plain text file (like XML). You can import XML feeds and also the very common JSON format as well. David Goodmanson explained VLOOKUP() for back in 2009. It’s been a long time coming but finally we have it. VLOOKUP() needs the source list to be sorted but there was no simple way to ensure a list was always sorted after a refresh. You can filter the data, change values to suit special cases, merge feeds and you can resort. The management of the incoming feed is soooo much better. It’s not just SQL Server and primitive web page scraping anymore. The range of data sources is much improved. The data links can be ‘one-off’ or updateable.
From there you can work with the data in a standard worksheet, PivotTable, PivotChart etc. The ‘bottom line’, as they say, is this: you now have a lot more powerful, yet easier, options for getting data from other sources, arranging it and adding to Excel. Promoting these new features isn’t helped by eye-glazing descriptions like this “… enhances self-service business intelligence (BI) for Excel with an intuitive and consistent experience for discovering, combining, and refining data across a wide variety of sources including relational, structured and semi-structured,” And there’s been bewildering name changes, so you’re forgiven for not following developments. It’s happened gradually and not always communicated well to customers. Microsoft has been improving the data connection features in Excel, especially Excel for Windows. Currency Exchange Rates and Stock/Index data can automatically update your Excel worksheets. We’re in the middle of a series on getting live internet data directly into Excel and it’s been a pleasant surprise.
0 kommentar(er)
