Peter Charles Limited created a detailed financial model for one of its customers. The model, implemented in MS Excel, included links to a number of external file-based data sources. To improve portability of the model, we wanted to move these external files into a secure cloud-based location.
The brief to the technical team at Peter Charles was to ensure that querying and modelling activity was kept within MS Excel. This would ensure that any updates could be carried out within MS Excel by the customer's finance staff.
This led us to a solution which makes use of MS Excel's ability to connect to external spreadsheets stored in Azure Blob storage.
- Analyse the queries in the MS Excel model to identify external data sources
- Create and configure an Azure Storage account
- Create and configure a Blob Storage container
- Configure synchronization between the Blob Storage location and file storage accessible to the customer's staff
- Modify the MS Excel model's queries to connect to the Blob Storage
- Test and deploy the model
- The Excel model is more portable as external files are stored centrally rather than copied locally
- Central management of external files means that everyone can be sure the model is representing a single version of the truth.
- Queries can be reused without modification on any PowerQuery platform (Excel, PowerBI, AAS)
- If desired, queries can be developed on a faster-performing platform such as PowerBI, then simply copied to Excel for use and fine tuning