How best to work with External Data and Microsoft Excel

I have been working with data for well over 34 years. I have seen a lot of changes happening especially working with external data sources. These external data sources can be ERP system or Data warehouse (Snowflake, Oracle, etc). The biggest challenge with any data is the format of that data (structure, publishing of the data, network connection, etc..

I have looked at different ways to pull external data for my metric reporting. Here are examples of my metric reporting:

The problem that I was faced with the above metric reporting is that I am getting the data from websites (copying & pasting) or CSV Files or PDF Files (Manual Input). In some cases I had to manipulate the file as the data was not in an usable format that I could consume for my reporting.

I have primarily used vlookup option of Microsoft Excel as part of working with my metric data. Here is a bit more about vlookup:

What is the Vlookup function?

VLOOKUP stands for 'Vertical Lookup'. It is a function that makes Excel search for a certain value in a column (the so called 'table array'), in order to return a value from a different column in the same row. This article will teach you how to use the VLOOKUP function.

What are the limitations of VLOOKUP?

1. VLOOKUP is only able to look up values to the right of the referenced cell.
2. VLOOKUP only returns the first instance where the criteria is met.
3. Sometimes data needs to be re-formatted before it can be used.
4. Data table MUST be referenced in a specific way.
5. Cells containing errors may cause issues.
6. Text vs numerical
7. Complex data may not look up properly

What are the benefits of VLOOKUP?

1. Allows for matching of simple data as part of pulling data for financial reporting
2. Allows users to work with external data from ERP systems as part of integrated data related to customer orders, vendor purchase information, SKUS, etc.

https://www.ablebits.com/office-addins-blog/xlookup-vs-vlookup/

Why should you moved away from VLOOKUP?

1. One of the biggest issues with using VLOOKUP is that it uses a static column reference. On the other hand, INDEX MATCH uses a dynamic column reference which means it can lead to fewer errors when moving around columns. This is an advantage that becomes more apparent as you create larger and more complex spreadsheets.
2. Time spent to convert data from source files into a usable format.
3. OLAP tools are being leverage as part of driving better business outcomes through data and AI. Excel add-in have been developed for tools like Onestream, Board, IBM Planning Analytics, datarails, and other tools
4. Using tools like kiraa.ai as part of file management - https://www.knowledge-orchestrator.com/product/
5. Using join tools like Alteryx https://knowledge.alteryx.com/index/s/article/Alteryx-for-Excel-Users-How-to-do-a-VLOOKUP-in-Alteryx-1583460632739

Financial Planning and Analysis team continues to facing increasing demand. This is perfect time to have a health check of your FPA function to see how data can better managed and leverage as part of driving better business outcomes from the data.

?

Paul Young CPA CGA is a former Senior IBM Customer Success Manager that has deployed over 300 data and AI solutions across geographies for the past 8 years. Paul is also FPA SME on how best to integrate macro and micro indicators as part of the data journey for the operational, management, and regulatory reporting cycles.

?

[email protected]

Blog – How to select the best ESG solution for your /Organization - https://www.dhirubhai.net/pulse/how-select-right-environmental-social-governance-solution-paul-young-83mzc/

EBITDA - Medium - https://medium.com/@paulyoung_81567/what-is-ebitda-and-why-is-edbita-important-to-organizations-daeda18ad017

Linked in – https://www.dhirubhai.net/posts/paul-young-055632b_what-is-ebitda-and-why-is-edbita-important-activity-7182946656424747009-Vh7k?utm_source=share&utm_medium=member_desktop?

Courses - https://www.dhirubhai.net/posts/paul-young-055632b_activity-7163302861974519809-ryf3?utm_source=share&utm_medium=member_desktop

?

?

What is EBITDA and Why is EDBITA Important to Organizations

Medium - https://medium.com/@paulyoung_81567/what-is-ebitda-and-why-is-edbita-important-to-organizations-daeda18ad017

Linked in – https://www.dhirubhai.net/posts/paul-young-055632b_what-is-ebitda-and-why-is-edbita-important-activity-7182946656424747009-Vh7k?utm_source=share&utm_medium=member_desktop