Analyze Financial Data with the Modern Excel #1

Financial data is widely available online. It’s a great resource to demonstrate and allow you to get started with Power Query and Power Pivot’s game changing capabilities, to ultimately make YOU, the business analyst / Excel pro, 100 times more powerful and valuable.

In this first session of our series on stock market analysis using the Excel Power BI add-ins, we create a direct connection to Apple’s (AAPL) historical stock prices in Yahoo Finance. The connection is created with Power Query. We then upload the data to the Power Pivot data model.

Here’s the link to access Apple’s historical prices from Yahoo Finance.

Stay tuned for the next session, in which we’ll start using Measures (KPIs) in Power Pivot!

Will



Our Rio 2016 Olympic Medals Report

Using Power BI’s powerful Query Editor feature (Power Query in Excel), we scraped the internet for some relevant data to analyze the Olympic results in Rio.

Rio 2016 Medals – Official Rio 2016 Website

London 2012 Medals – BBC

Number of athletes by country – Wikipedia

Population – Wikipedia

GDP – Wikipedia

Enjoy!

PS Good internet connection required 🙂



Building a beautiful non-Excel-looking Dashboard using…Excel (part 3)

When dealing with a client that has Excel 2010, 2013 or 2016, the Sensdat team builds and formats the dashboard with Excel of course, using Pivot Tables, slicers, hyperlinks and conditional formatting. This blog post is about visuals only, what we call the “window” that we create and set on the very messy and dark “data-Mordor” 🙂  . The “back-end stuff” (data modelling, Kpis, etc…) is of course made with our favorite tools: Power Pivot and Power Query but does not interest us here.  The following process will help you save a lot of time and repetitive tasks while creating an awesome cockpit that will make all of your colleagues admire your skills! 🙂

In our last post, we looked at “adding some insightful content” to our already very sexy cockpit. Yeah! It was a long post. So, this one will be a short one, promised!

Continue reading “Building a beautiful non-Excel-looking Dashboard using…Excel (part 3)”