Loading...

Follow My Online Training Hub on Feedspot

Continue with Google
Continue with Facebook
or

Valid

Splitting data over multiple sheets is perhaps one of the worst Excel crimes I see. It’s a crime because it breaks the rule that source data should be in a tabular format. Tabular data is what we need for PivotTables and many functions like SUMIFS, COUNTIFS, INDEX, VLOOKUP etc.

To have your data in any other format is just going to make your Excel life difficult. Anyhow, that’s enough ranting about data layouts. I do enough of that here.

Thankfully we can easily consolidate Excel sheets with Power Query in just a few clicks of the mouse.

Power Query Consolidate Excel Sheets

Let’s take the file below that has a separate sheet for each salesperson’s order data (Buxton, Maxwell, Jarvis and Everton), and another sheet containing ‘Other Stuff’ 1:

1. The ‘Other Stuff’ sheet simply represents a typical file that has another sheet(s) containing information that isn’t source data2. I included this sheet so that I can address how to handle these sheets when consolidating source data in Power Query.

2. Source Data is data that I want to include in my consolidated data set. It’s the source of your analysis.

Key Points to Using Power Query Consolidate Excel Sheets

Ideally, we want the source data on the four salesperson’s sheets merged into one sheet because that’s going to allow me to summarise it with a PivotTable or easily analyse it with any of the built in Excel functions, which is not possible when the data is spread across multiple sheets.

The technique I’m going to show you here requires:

  1. The data on the sheets I want to consolidate are formatted in an Excel Table or has a been given a Named Range. This is required for Power Query to find the data.
  2. The table structure (column names) on each sheet you want to consolidate are the same.
  3. The name of the Tables or Named Ranges use distinct nomenclature that is different to any tables/ranges you don’t want to include. E.g. my salesperson tables all begin with ‘Orders…’, as you can see in the Name Manager below:

    Note: The ‘TotalOrders’ table is on the ‘Other Stuff’ sheet and purposely doesn’t begin with ‘Orders’ because it isn't part of my source data. This will allow me to filter the tables and consolidate only those that have names beginning with ‘Orders’.

Consolidating Excel Sheets using Power Query

Ok, now the housekeeping tasks are out of the way, let's look at how we can use Power Query to grab the data off the salesperson sheets and merge it into one table:

Watch the Video

Consolidate Excel Sheets with Power Query - YouTube

Written Instructions Step 1: Create a new blank query in the file containing the sheets you want to consolidate. For Excel 2016 or Office 365 take the following steps:

In Excel 2010 or 2013 take the following steps:

Note: If you don’t see the Power Query tab in Excel 2010 or 2013 you can download it here.

This opens the Power Query Editor window.

Step 2: In the formula bar type:
=Excel.CurrentWorkbook()

As shown below:

Note: Power Query functions are case sensitive.

Step 3:

Press ENTER. This returns a list of the Excel Tables, Named Ranges and Filtered Lists in your file:

Step 4: Filter the ‘Name’ column for items that ‘Begins With…’:

In the ‘begins with’ field enter ‘Orders’:

Using a consistent nomenclature allows me to add more ‘Orders…’ tables to my file in future and Power Query will automatically include them upon refresh. Of course, if I’m sure I’ll never add any more sheets that I want to include in the consolidated table then I could simply use the check boxes in the Filter list to select the tables I want.

Step 5: Now I only have the tables I want to consolidate, I can click the double headed arrow on the ‘content’ column to expand the data:

Note: If you don’t deselect ‘Use original column name as prefix’ each column header will be prefixed with ‘Content’. E.g. Content.Country, Content.Salesperson etc.

And with that I have my data consolidated into one table:

Notice that the last column contains the name of the source Table. You can click on this column header and DELETE it if you don’t want it.

Step 6: Set the Data Types. It’s important to tell Power Query what type of data you have in each column; dates, text, decimal numbers etc. To do this, click on the icons in the top left of each column and select the data type from the list:

Note: This isn’t formatting, as that’s done in the Excel sheet. These are data types required by Power Pivot (if you’re using it) and will help Excel identify what cell formatting it can automatically apply if any.

Step 7: We’re almost done. Give your query a name. This name will be inherited by the Excel or Power Pivot Table, so choose carefully avoiding the nomenclature for the tables you are consolidating. i.e. don’t begin with ‘Orders’, otherwise this table will be included in the query and you’ll double count your data!

Step 8: Now we’re ready to load the data into Excel or Power Pivot. On the Power Query Editor Home tab > Close & Load > Close & Load To…:

Step 9: Choose the destination for your data:

Note: Excel 2010 and 2013 will not have PivotTable Report or PivotChart, as listed above. Excel 2010 will not have the ‘Add his data to the Data Model’ option.

I chose to load my data into a Table in a new worksheet, as you can see below:

It’s important to note that Power Query does not alter the original tables. It merely takes a copy of the data and creates a new table for you to work with. If data in the original tables gets updated, you can simply refresh the query (Data tab > Refresh all) and it will update the consolidated table.

You can also get the data from other Excel files and create a new file for the consolidated data. This helps keep your file size manageable if you’re working with a lot of data.

Learn More Power Query

Everyone should learn Power Query. It really is a game changer for automating tasks and making light work of laborious jobs.

Don’t forget that my friend Jeff Lenning at Excel University is running a free Power Query webinar where he’ll show you some more incredible things you can automate with Power Query.

And if you’d like to learn Power Query take a moment to check out my course, it’s available at 20% off for a limited time:

Workbook Download

Enter your email address below to download the sample workbook.

Get Workbook
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.
Please Share

If you liked this please click the buttons below to share.

The post Power Query Consolidate Excel Sheets appeared first on My Online Training Hub.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

The Power Query Add Column from Example feature simplifies a task many of us perform daily. For example, how often do you add a column that references another column; either to extract part of it, perform a calculation or convert the data into something else? Or maybe you want to join columns together?

Let’s take column A in the example Table below that includes the name and position for each employee, which I want to separate into their own columns:

This task is very easy with Power Query. In the animated image below, I used the Power Query Add Column from Example feature to extract the position and name from the first column by simply giving Power Query an example.

Note: the image above uses Excel 2016/Office 365. For earlier versions of Excel you'll find Power Query is a free add-in (download it here) with its own tab on the ribbon:

In more complex data sets you may need to give Power Query two or three examples before it can correctly detect the pattern. But that’s way easier than writing the formulas yourself.

What’s also nice about this is you can see the formula Power Query has written for you In the Power Query Add Column from Example screen below. So, it’s a great way to learn the Power Query formula language.

Not Impressed? What…you’re not that impressed with the Power Query Add Column From Example feature? Wow, tough crowd. Ok, let me show you another example. Below is some sales data by month for various regions. I want to calculate a rough margin of 10% and then add a column with some comments:

I think you’ll agree that it’s quite amazing.

Ok, I know the 10% calculation isn’t that difficult to do in Excel, but it’s more efficient to shift formulas like this to Power Query because they are only calculated upon refresh of your data. Likewise, IF and VLOOKUP type formulas are much more efficient in Power Query.

Power Query Add Column from Example isn’t limited to the examples shown here. You can perform many different transformations, including a plethora relating to dates.

Just take a look at the screenshot below; you can see I typed ‘week’ into the example field in Column1 and I'm presented with a list of week related information I can extract from the date field, including the name of the weekday, the week number and more as shown in the intellisense list below:

Loading the Data

When you’re finished adding columns and performing other data cleaning tasks in Power Query you can ‘Close and Load’ the data to various locations, as shown in the Excel 2016/Office 365 dialog box below:

Note: The Close and Load To dialog box may have less options in earlier versions of Excel.

Updating Queries

You’re probably wondering what happens when you change or add new data to your original (blue) table. For example, I’ve added a new employee to the table in row 11 and changed the position for Anne Smith on row 7:

And now all I need to do is right-click the (green) output Table of the query and click Refresh. Power Query grabs the updates in the blue table and adds the Position and Name columns before loading it to the green table:

That’s two clicks! TWO CLICKS to update EVERYTHING. And it doesn’t matter how many transformations I make in Power Query. Updating queries is always as simple as two clicks.

Because just like a macro it is reusable again and again.

You can even set the refresh to happen automatically if you want…but you don’t want to make yourself completely redundant

Data From Other Sources

In these examples my data is in a Table in the Excel file, but Power Query can get data from a vast range of sources including CSV and text files, other Excel files, databases and more. The image below shows just some of the locations you can get data from:

Power Query Free Webinar + Course 20% Off

Everyone should learn Power Query because it simplifies and automates repetitive and labor intensive tasks that all Excel users perform. OK, maybe not everyone, but I'd estimate 95% of us. Just yesterday I got an email from one of my Power Query course members saying how she used Power Query to consolidate 4 trial balance reports, saving a load of time and it has made her look like an expert!

Don't forget that my friend Jeff Lenning at Excel University is running a free Power Query webinar where he'll show you some more incredible things you can automate with Power Query.

If you'd like to learn Power Query, please take a moment to check out my Power Query course, it's currently 20% off (discount already applied). Tip: get more than one course and take 20% off them too*.

Or for accountants and other professionals who require CPE credits my course is also available through Excel University with 12 CPE credits. Use coupon PQ20 to get 20% off at Excel University.

*Additional 20% off other courses applies to courses purchased through My Online Training Hub.

Workbook Download

Enter your email address below to download the sample workbook.

Get Workbook
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.
Please Share

If you liked this please click the buttons below to share.

The post Excel Power Query Add Column from Example appeared first on My Online Training Hub.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

From time to time I get asked which Microsoft Office licence* should I get; Office 365 subscription, or the one-time payment for a perpetual licence i.e. perpetual licences are the ones you pay for once and own forever…or until you decide to upgrade.

The answer is simple really; perpetual licences are dead. There I said it. I know you don’t want to hear it and ok, yes you can still buy perpetual licences, but for how much longer remains to be seen.

Before the perpetual licence fans out there throw their toys out of the cot, it’s not as bad as it sounds. In fact, subscription licences are a far better deal in a lot of instances. Especially if you like to upgrade your software every 3 years or so.

*Before you call me out on my spelling of licence I’d like to explain that in British English there are two spellings; the noun is licence, whereas the verb is license. For the most part I’ll be referring to it as a noun, hence my spelling.

Office 365 vs Perpetual Cost Comparison

And we’re back…the accountant in me likes to compare numbers. I looked at home/personal licences and business licences and in the table below I compared the annual cost over 3 years (see the last column):

Now, this is just a handful of the product options available because to show you them all would be like drinking from a fire hydrant.

And anyway, the point I want to make here is that the cost comparison over 3 years of ownership is pretty close and in some cases Office 365 is a ridiculous bargain.

For example, if you have 2 or more PC’s in your household then Office 365 Home is the best value. If you use all 5 licences, then it’s $20 per person/device per year. Even if you only have 2 users or 2 devices it’s still cheaper than the perpetual licence.

And if you’re a business user then Office Home & Business might be slightly cheaper than the Office 365 Business option, but you don’t get Outlook, Project, Access and OneDrive, so it’s not comparing like for like.

Whichever option you choose, Office 365 is a fraction of the cost of one cup of coffee per week. And I haven’t even gone into the deep accounting woods by comparing the costs based on the Time Value of Money. i.e. paying up front vs spreading the cost over time, which would make the subscription licence even more attractive.

Office 365 vs Perpetual Benefit Comparison

Everything the perpetual licences have are available with the Office 365 licences, but the same can’t be said the other way around.

The biggest benefit to getting an Office 365 licence are the updates with new features. Office 365 users get new features, perpetual versions don’t. Every time I write a newsletter about a new Excel feature I get a swathe of people emailing me to ask where they can find it in their perpetual version of Excel. It’s never fun replying to those emails because I know I am the bearer of bad news.

Office 365 Excel Updates

So far this year (2018) we Office 365 users have enjoyed the following updates:

Deselect cells: Make selections in your worksheet and deselect cells that you accidentally clicked without having to start over. “I never accidentally select the wrong cells”, said no one ever! You know you want this.

PivotTable Default to SUM instead of COUNT: Back in the day, ok last month, if you put a field in the values area of a PivotTable that had any empty cells it would COUNT the data instead of summing it. Even if every other cell contained a number. It was infuriating, but now if you add a numeric field to the values area it will default to SUM. Hooray! Of course, if you have some cells that contain text then you’re going to get a COUNT, but then that’s fair enough.

Chat with co-authors while you edit: Collaborate more effectively by chatting with your co-authors without ever leaving Excel (desktop or online versions of Excel). Yes, Google Sheets isn’t the only tool offering online collaboration.

Quick Insights: Have Excel generate interesting visualizations based on your data. Excel uses Machine Learning to interpret your data and detect patterns, outliers and other interesting insights. No chart experience required. I’ll be writing about this feature in a newsletter soon. It’s quite amazing.

New Data Types (in preview): AI-powered data types pull data from online sources e.g. stock prices and geographical data. More Types will be added in future.

Microsoft Translator: Translate words, phrases or sentences to another language with Microsoft Translator. You can do this from the Review tab in the ribbon.

And some key features released in 2017 were:

Personalize the default PivotTable layout: Set up a PivotTable the way you like and start with that layout every time you create a new PivotTable.

3D models: Use 3D to increase the visual and creative impact of your workbooks.  Easily insert a 3D model, then you can rotate it through 360 degrees.

Power Query Add Column from Examples: Users can create new columns by example and split table columns into rows.

And that’s just the Excel updates. Don’t forget Word, PowerPoint, Outlook etc. also got updates. My favourite Word update recently is the built in Dictate tool. I may never type again

Office 365 vs Perpetual Misconceptions

Some other common concerns I hear about the subscription plans, aside from the distaste in paying for it every month or once a year, surround access to the software.

To be clear; the software is still installed locally on your machine. You do not have to be connected to the internet to use it.

Microsoft aren’t the only company moving to a subscription pricing model. Adobe did it in 2017. Many phone apps have a monthly subscription. It’s the way of the future.

I think the biggest objection for moving to the Office 365 subscription plan is the dislike of paying monthly for something you used to buy and have forever. But you didn’t really have it forever because the majority upgrade their software every 3 years.

It’s time to look at software pricing differently, embrace the subscription model and reap the benefits of new features as soon as they become available. It’s the way of the future. Don’t be held back with a perpetual licence.

What Version do I Have?

If you’re not sure what version you have, go to the File tab of any Office application > Account.

Under ‘Product Information’ it will tell you if you have the subscription version of Office 365 and what SKU. In the image below, you can see I’m on the Office 365 subscription and my SKU is ProPlus:

Office 365 users may have noticed that upon start up, Excel and other Office applications no longer say ‘Excel 2016’, instead it simply says ‘Office 365’:

Note: As an MVP I don’t get any rewards (financial or non-financial) for encouraging you to switch to the subscription model. It’s just my personal recommendation.

Please Share

If you liked this please click the buttons below to share.

The post Office 365 vs Perpetual Licences appeared first on My Online Training Hub.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Last week I wrote about getting real time data into Excel using PyXLL and Python code.

I described a way to do this using HTTP connections. HTTP is the type of connection your browser uses to read web pages.

In this article I'm looking at using sockets to get real time data into Excel.

Sockets

A socket is connection made between a client device (e.g. your PC) and a server (e.g. a website, a server, a sensor, etc) that allows the client and server to send each other messages.

This socket connection, once established, stays open and requires little overhead to send messages whilst open.

Think of it like this. You call your best friend on the phone to have a chat, but when you are finished talking, you keep the call connected in case you think of something else you want to talk about in a minute. By not hanging up, you don't have to dial the number again and start another conversation when you think of something new to discuss.

Compare this with a HTTP connection. If your PC (the client) wants to read a web page from our blog you have to click a link. By clicking this link you are telling your browser to request the contents of the blog post from our website (the server) and display it on your screen.

Before any part of the blog post can be sent to you, the browser has to send a request to the server asking it for the blog post. The server then responds informing your browser if it is able to send the blog, before it actually starts to send any part of the page.

Every time you click a link to load another blog post (or any web page) your browser and our website have to go through this whole routine again.

Compared to sockets, the HTTP connection has more overhead in its communications. Every time a page is requested, the browser and server have to talk to each other to set up the connection before any data can be sent.

Once a socket is established it remains open and the server can just send a message down it to the client. The client doesn't have to keep asking the server for the next piece of information.

For real time systems this is beneficial as the client can just sit there and wait for the next piece of data to arrive, like a change in a stock price, or the latest reading from a sensor.

Implementing Sockets in Excel With PyXLL and Python

I'm again going to use the PyXll Python add-in. You'll need to install both PyXLL and Python if you want to give this a go.

As explained in the last post, my Python code will use a modification of Excel's RTD class to pass data into the worksheet. Python works away in the background and notifies Excel when new data has been received which is then displayed on the sheet.

For this exercise I'm going to use the websockets API provided by GDAX to monitor, amongst other things, the price of Bitcoin in USD.

To set up the socket I'll need to install the websocket-client package in Python.

The Code

Once you've imported the socket library into Python you create the socket like this

To get updates from GDAX on the Bitcoin-USD price, subscribe to the ticker channel, specifying the currency pair BTC-USD.

You can subscribe to multiple currency pairs here in the same request if you wish.

All the heavy lifting is done by the GDAXRTD class

and it's in __thread_func that we listen for updates from GDAX and then process the messages we receive.

Any message we receive is in the form of a JSON object and is stored in a variable called result.

The JSON data looks like this

{"type":"ticker","sequence":5981458471,"product_id":"BTC-USD","price":"7361.05000000","open_24h":"7493.86000000","volume_24h":"5764.09338556","low_24h":"7272.71000000","high_24h":"7538.14000000","volume_30d":"273076.23540644","best_bid":"7361.04","best_ask":"7361.05","side":"buy","time":"2018-05-31T03:20:03.484000Z","trade_id":44233953,"last_size":"0.13707180"}

Processing the Data Updates

PyXLL can only return a single piece of data to Excel. If we are trying to get the current price, bid and ask prices, how to do this?

By converting the data object sent to us by GDAX into a string, we can return that string to Excel and then use string functions like MID and FIND to extract the values we want.

Once we've extracted these numerical values, we can use the VALUE function to convert the number stored as a string, into a number.

In my sample workbook all of these formulae are included for you to examine.

Here's what the sheet looks like with the real time data updates coming in.

Charting This Data

As we are only able to send one value to Excel at any one time, the numbers we are getting are points in time which we can't produce a meaningful chart from. In order to create useful visualizations of the data, we need to store the data as it changes over time.

We could do this by writing some VBA to add every updated value to a dynamic named range, and chart that.

We could write the data to a file and chart that but we'd lose the ability to see the data in real time.

There are other solutions we could use involving Python too, but for now I'll just leave it up to you to figure this out and maybe I'll write another post dealing with this.

Visual Studio Code

I had to reinstall Anaconda for this post and in doing so also installed Microsoft's Visual Studio Code source code editor and so far it has been great. All the images of code above were taken from it.

As you can see from the screen shots above, it obviously has color coding, and also provides debugging and intellisense amongst other things. If you are looking for a code editor, give it a go, it's free.

Download the Workbook and Python Code

My workbook and sample Python code are provided for download as a ZIP file. Enter your email address below to get these.

Get Excel Workbook
By submitting your email address you agree that we can email you our Excel newsletter. You can unsubscribe at any time.
Please enter a valid email address.
Excel Workbook & Python Code (Zip Archive)
Real-Time-Data-In-Excel-Using-Sockets.zip

The post Real Time Data in Excel Using Sockets appeared first on My Online Training Hub.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Excel does support getting real time data into the worksheet with the RTD function, but this requires programming a COM server to which the RTD function communicates.

Power BI is able to make use of streaming data via API connections or PubNub, but what if I just want to pull some live data directly into my worksheet?

With PyXLL I can do just that. PyXLL is an add-in that allows you to write Python code and use the results in Excel. Not only can you pull in real time data, you can write your own functions, amongst other things, making use of the many Python libraries available.

PyXLL uses a class derived from RTD which when combined with your own Python code, allows you to stream real time data.

Before You Start

You will of course need an installation of Python on your PC that will be used by PyXLL in Excel. Download and install the latest version from here : Get Python - it's free!

Once that's done you can get PyXLL from here : PyXLL, which is available for a 30 day free trial, after that there is a charge for continuing to use it.

Real Time Data

The world is awash with mentions of IoT (Internet of Things) streaming data from devices like sensors that tell you the temperature of the water in your fish tank, to what your cows are doing.

If you had a stream of data from an IoT device you could certainly use the following method to read and present the data in Excel. But in this post I'm going to write code for two examples, the price of crypto currencies, and some random numbers from a web based random number generator.

API's

To get such information it can be made available by what's known as an API - application programming interface. Which is a way of asking for and getting some data from a program, website or some other similar service.

For the crypto currencies, CoinCap provide a simple API which gives you information including the price for the currency you specify.

If I want to get the price of Bitcoin all I need to do is visit https://coincap.io/page/BTC

If you click that link, rather than see a nicely laid out website, you'll just see a bunch of text like this

{"altCap":199266689173.458,"bitnodesCount":9960,"btcCap":127140496841.25,"btcPrice":7456.21,"dom":52.37,"totalCap":326407186014.7091,"volumeAlt":870559159.7352718,"volumeBtc":957025490.8778814,"volumeTotal":1827584650.6131513,"id":"BTC","type":"cmc","_id":"179bd7dc-72b3-4eee-b373-e719a9489ed9","price_btc":1,"price_eth":12.964822877478024,"price_ltc":63.283392645849474,"price_zec":27.135394004101762,"price_eur":6279.284343063262,"price_usd":7456.21,"market_cap":127140496841.25,"cap24hrChange":-5.47,"display_name":"Bitcoin","status":"available","supply":17051625,"volume":6331440000,"price":7456.21,"vwap_h24":7647.663539685362,"rank":1,"alt_name":"bitcoin"}

That's because the website is expecting to be sending the data back to a program so has formatted it into a JSON response the program can easily use. This is not meant for human eyes so isn't meant to look pretty. When we get this data we manipulate it in our Python code and return whatever we want to Excel, which in this case is the price.

If you want to you could make use of all the other information too but I just want the price for this example.

So by making a call to the CoinCap API for each currency I specify, it sends me the informaton related to that crypto currency.

It works the same way for the random number generator. My Python code makes a call to the random number generator at https://random.org, and the website sends me back a random integer.

Try it yourself https://www.random.org/integers/?num=1&min=1&max=99&col=1&base=10&format=plain&rnd=new

PyXLL RTD Class

By adding this RTD class into your code and modifying __thread_func to do the work of calling the API's and processing the result, we get our code to update our currency prices and random numbers in real time.

Crypto Currency Price Updates

    def __thread_func(self):
        while self.__running:
            # Setting 'value' on an RTD instance triggers an update in Excel
            try:
                opener = urllib.request.build_opener()
                opener.addheaders = [('User-Agent', 'Mozilla/5.0')]
                opener.addheaders = [('Cache-Control', 'max-age=0')]
                url = "https://coincap.io/page/%s" % self.__symbol
                response = opener.open(url)
                data = response.read()
                data = data.decode()

                # the returned data is in json format
                result = json.loads(data).get("price", "#NoLatestPrice")
            except Exception as e:
                result = e
            new_value = result
            if self.value != new_value:
                self.value = new_value
            time.sleep(3)

Random Number Generator

    def __thread_func(self):
        while self.__running:
            # Setting 'value' on an RTD instance triggers an update in Excel
            try:
                url = "https://www.random.org/integers/?num=1&min=1&max=99&col=1&base=10&format=plain&rnd=new"
                data = urllib.request.urlopen(url).read()
                result = data.decode()
            except Exception as e:
                result = e
            self.value = int(result)
            time.sleep(random.randrange(2,10))
Calling the Functions

By defining a function for both of these bits of code through PyXLL, Excel is able to access them like it would any other workbook function.

This will give you the price for the crypto currency specified by the 3 letter currency code (BTC = Bitcoin)

=coin_price_rtd("BTC")

You can pass in a cell reference rather than a string if you want to e.g.

=coin_price_rtd(A1)

Where A1 contains the 3 letter currency code.

This will generate random numbers and doesn't require any arguments

=random_rtd()

Here they are in action

I've added some conditional formatting to make the numbers look a little bit more interesting and visually appealing.

The top 50% of the currencies by price are green, the bottom 50% are red.

For the random numbers, any number greater than the average of all the numbers, is green.

Download the Excel Workbook and Python Code

In order to get this to work you'll need an installation of Python and to have installed the PyXLL add-in as described at the top of this article.

My workbook and sample Python code are provided for download as a ZIP file. Enter your email address below to get these.

Get Excel Workbook
By submitting your email address you agree that we can email you our Excel newsletter. You can unsubscribe at any time.
Please enter a valid email address.
Excel Workbook & Python Code Real-Time-Data-Excel.zip
Summary

If you know Python or want to try your hand at it, PyXLL is definitely worth a look. And if you want to get real time data into Excel it looks like a good solution.

Using Python it's possible to get real time data in other ways like Socket.IO and PubNub too. I plan to look at these in future posts.

The post Real Time Data in Excel appeared first on My Online Training Hub.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
My Online Training Hub by Mynda Treacy - 1M ago

Excel mini charts are a great alternative to Sparklines.

Mini charts are just regular charts made really small. You might consider using them instead of Sparklines when you want something other than column, line or win/loss charts. For example, the table below contains mini area charts:

Ok, so an area chart isn’t much different to a line chart. But with Sparklines you can’t display two series in the one chart, like these budget and actual mini charts below:

And there’s no Sparkline equivalent for this variance chart below:

Excel Mini Chart Workbook Download

Enter your email address below to download the sample workbook.

Get Workbook
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.
Inserting Excel Mini Charts

There are a few tricks to creating Excel mini charts. Especially because we want them to resize automatically with the row and column height.

Step 1: Select the chart data > Insert tab > choose your chart.

Step 2: Remove the title, axes and grid lines. Basically, you want to remove any clutter from the chart because you don’t have space to display it in a mini-chart:

Step 3: Resize the plot area so that it extends to the outer edge of the chart area…you need every millimetre of space for the mini chart:

Step 4: Hold down the ALT key while you left click and drag the outer edge of the chart to the cell you want it in. Then resize the chart to fit in the cell while holding down ALT. This will snap the chart to the cell so that if you resize the row/column the chart will also resize.

Rinse and repeat for additional cells.

Below is an animated image of the steps listed above:

The variance chart is slightly different in that it’s a single chart plotting just the variance values in column D:

Note: in the variance chart above I selected the negative bars and individually changed their fill color to red. However, if this chart is likely to change regularly then you can automate this process by using two series in the chart; one for negative values and one for positive, which you then color accordingly.

Examples are in the Excel file available for download towards the top of this post.

Tip: for presentation purposes you can hide column D and just use the chart to convey the variances.

Excel Mini Chart Tips
  • It doesn’t suit every chart type - while you can make any chart into a mini chart, not all charts are suitable for a small space.
  • Go easy on the formatting – you’ve got a small space so don’t clutter it with unnecessary formatting.
Related Tutorials

Sparklines

In-cell Charts

Chart Formatting Tips

The post Excel Mini Charts appeared first on My Online Training Hub.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Excel multi-colored line charts are a little gimmicky, after all we don’t need color to tell if a line is going up or down. However, it can be useful for encoding data that is outside a threshold and can also speed up interpretation, especially if you use familiar traffic light color encoding.

We have a few different approaches available for creating Excel multi-colored line charts.

Excel Multi-colored Line Charts Workbook

Enter your email address below to download the sample workbook.

Get Workbook
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.
Option 1: Multi-colored line chart with Gradient Fill

The first is to use a gradient fill on the line. This is the simplest as it only requires a single series:

With the line selected press CTRL+1 to open the Format Data Series Pane. In the Format menu (bucket icon) for the line, choose ‘Gradient Fill’:

Adjust the gradient stops, adding and removing stops as required with the +/- icons to the right of the gradient bar. Select each stop to set the color.

The limitation with the gradient is that it’s based on percentages, as opposed to absolute values. Which means you can’t set values above or below a threshold with a specific color, and this makes updating the gradient stops for new data a (potentially) manual task. It really depends if you plan to update your chart with new data or not.

Option 2: Multi-colored line chart with multiple series

The second option for Excel multi-colored line charts is to use multiple series; one for each color. The chart below contains 3 lines; red, yellow and green. They are sitting on top of one another to give the appearance of a single line.

This requires your source data to be set up with each series in its own column, like so:

The #N/A values aren’t plotted and that allows the lines underneath to show through where appropriate.

To ensure a continuous line the series must overlap, hence row 16 above has the same value in both columns D and E. Without the value in column E, there would be a gap in the line.

For ease of calculation the green series plots every value, but it is covered by the yellow and orange series where appropriate. This requires the series to be in the right order in the legend entries (image below), with green at the top of the list, then yellow, then orange:

Tip: Use the up/down arrows to rearrange the order of each series as required.

Option 3: Threshold bands with line

The third option uses a stacked area chart for the threshold bands, with a white line to show the position of the value at each interval:

This requires 4 series; one for each band + the line (column B):

Insert a Stacked Area Chart to start and then right-click the series you want as the line > Change Series Chart Type…:

In the Change Chart Type dialog box set it to a Line chart:

Then you can go about setting the line and area colors for each series.

Option 4: Multi-colored columns with multiple series

Ok, so it’s not a line chart, but it has a similar effect because we can easily get a feel for the trend from the height of the columns:

Again, like option two, this requires three series to support the different colored columns.

Warning

Don’t get carried away using gradient fills, multi-colored lines and columns etc. Please only use them where they aid interpretation otherwise they fall into the ‘Chart Junk‘ category. At best they can make you look unprofessional and at worst make it difficult for your audience to interpret the chart.

Please Share

If you liked this please click the buttons below to share.

The post Excel Multi-colored Line Charts appeared first on My Online Training Hub.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
My Online Training Hub by Mynda Treacy - 1M ago

Excel 3D Maps in 2016, or Power Map as it’s known in Excel 2013, enable you to visualise data in, you guessed it, 3D!

No surprises there, however you may be surprised to discover insights you didn’t anticipate. Because unlike 2D maps or tables, with 3D maps we can quickly identify hot spots, anomalies, clusters etc. and connect them to the physical world.

For example, the image below shows earthquake data revealed over time:

3D Maps aren’t limited to geographic data, we can also plot data on a custom map like this football pitch displaying 2 players as they move around the pitch. The height of the columns represents their speed:

Version Restrictions

Power Map is available in Excel 2013 ProPlus for Office 365 users.

3D Maps are available in all versions of Excel 2016 or Office 365 for Windows users. In other words, it’s not available for Mac users.

Note: Maps created in Excel 2016 cannot be opened in Excel 2013.

Tip: Power BI users have a similar custom map visualisation called Globe Map.

Download the Workbook

Enter your email address below to download the sample workbook.

Get Workbook
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

The file contains separate maps of the earthquake data for Excel 2013 and 2016. If you want the football data you can download it from the link at the bottom of this post. It's a large file, which is why I haven't included it for download here.

Creating Excel 3D Maps

Excel uses the Bing map engine to plot your data on the map and so it needs a connection to the internet for Excel to ‘talk’ to Bing.

It requires data in a tabular format. You can work with data in an Excel worksheet, or data already in the Data Model/Power Pivot.

If your data is in an Excel worksheet then select your data range > Insert > Power Map/3D Map. Excel will load the data to Power Pivot/Data model and open the 3D Map window.

If your data is already in Power Pivot/Data model you can go straight to Insert > Power Map/3D Map.

The 3D Map window (below) opens independently of the Excel window. It has the familiar ribbon and tabs found in all Office products.

The left-hand Tours pane (highlighted in pink) is where you organise your scenes, which are the different views of your data.

The middle section is your map and the right-hand Layer pane (highlighted in yellow) is where you specify the fields and settings for your map data.

A close up of the Layer Pane is shown below:

Layer Pane notes:

  • The layer pane options vary slightly depending on the visualization type (1) you choose.
  • The chart/visualisation types (1) available stacked column, clustered column, bubble, heatmap and custom regions (Custom Regions is available in Excel 2016/Office 365 only).
  • Bing can work with various location attributes (2), as listed here:
  • Note: the X and Y coordinates are for custom maps.

Excel 2013 Power Maps vs Excel 2016 3D Maps

For the most part the tools are the same. The most notable differences are that Excel 2013 doesn’t support custom maps, and you'll find some of the menus are a little different.

In Excel 2016 the right-hand Layer pane (shown earlier) contains all the setting options. Whereas the Excel 2013 layer pane is a little different, with tabs at the top to reveal different task panes.

The layer icon allows you to set the layer name, or add further layers:

The Field List icon is where you build your visualisation. It works much like the PivotTable field list:

And Layer Options and Scene Options are available under the gear icon:

Sharing Excel 3D Maps / Power Maps

We have three options for sharing 3D maps:

  1. Take a screenshot of the map. There’s a ‘Capture Screen’ icon on the ribbon to help you select the area you want:
 
  1. Create a tour video.
 

You can choose from various scene settings via the Scene Options icon in the Ribbon e.g. duration, and effects, even adding music if you want:

 
  1. Share the Excel file containing the 3D Map. When you insert a 3D map in your file, Excel adds a text box to alert the user that there is a 3D map in the file:
Updating 3D Maps

Clicking the Refresh icon in the ribbon (see image below) will refresh the connection to your source data:

And if your data is imported from an external source using Power Query it will also refresh that connection, bringing in any new data.

Learn Excel 3D Maps / Power Map

Learn tons of data visualisation techniques, including 3D Maps in detail, in my Excel Dashboard course.

Data sources

Earthquakes: https://en.wikipedia.org/wiki/List_of_21st-century_earthquakes

Football data: http://home.ifi.uio.no/paalh/dataset/alfheim/

Please Share

If you liked this please click the buttons below to share.

The post Excel 3D Maps appeared first on My Online Training Hub.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Applies to Excel 2013 onward for Power Pivot/Data Model or OLAP PivotTables.

Excel PivotTable Quick Explore allows us to filter and drill down in a PivotTable:

And Pivot Charts:

Excel PivotTable Quick Explore requires the source data to come from the Data Model (aka Power Pivot), or an OLAP (Online Analytical Processing) cube. In other words, it doesn’t work with regular PivotTables and only works in Excel 2013 onward where Power Pivot is built into Excel, as opposed to an add-in.

Download the Workbook

Enter your email address below to download the sample workbook.

Get Workbook
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.
Building PivotTables for Quick Explore

In the Create PivotTable dialog box (Insert tab > PivotTable), you can either connect to an external data source:

And navigate to your OLAP database, or other relational database like Access.

Or if your data is in an Excel Table you can check the box to load it to the Data Model upon creating the PivotTable:

Using Excel PivotTable Quick Explore

To use Quick Explore, first select the cell or item in the chart axis labels that you want to filter on, then click the magnifying glass, or right-click > Quick Explore. From here you can choose the table and field you want to drill down to.

In the example below notice that I’ve selected John, which will become my filter. Then I want to drill down to the Order Priority field:

And below you can see that John has become the Filter (cell B2) and the row labels contain the Order Priority:

We can continue to drill down further into the data. For example, from here we can filter Order Priority ‘High’ and drill down to the Product Container:

Now we have two filter items; Salesperson ‘John’ and Order Priority ‘High’, with the Product Container in the row labels:

If you have multiple tables in your Data Model/Power Pivot, then those will appear with an expand icon to the left of the table name. Clicking on this will reveal the fields in the table:

So, now that you’ve drilled down you’re probably wondering how you drill back up. Well, there’s no drill up as such, but you can use CTRL+Z or 'Undo' on the Quick Access Toolbar to undo the filter and drill down. But beware as this will only undo the last Drill Down.

Excel Quick Explore Pivot Charts

Quick Explore works the same in a Pivot Chart, except you select the item in the axis to trigger the Magnifying Glass icon:

Excel PivotTable Quick Explore Things to Note
  1. The hierarchy can come from the source data or grouped items, like dates etc.
  2. If you already have a hierarchy in your PivotTable it will be replaced with the field you drill down on.

    For example, in the PivotTable below I’ve selected 2014 to ‘Explore’ and I’m going to drill down to the Order Priority, which will replace the quarters currently in the row labels:

    In the image below, you can see the quarter breakdown has been removed from the PivotTable altogether:

Excel PivotTable Quick Explore Limitations
  • Your data needs to be in a hierarchy that supports drilling down. e.g. flat hierarchies or hierarchies that don’t have data on multiple levels are not supported.
  • You cannot drill down on named sets.
Alternatives to Quick Explore

If you want to retain the levels in the hierarchy as you drill down then an option is to use the Expand/Collapse buttons. These are available in Pivot Charts in Excel 2016/Office 365:

Learn about Excel Pivot Chart Drill Down Buttons here.

  Please Share

If you liked this please click the buttons below to share.

The post Excel PivotTable Quick Explore appeared first on My Online Training Hub.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

We had a post on the forum recently asking how to create a timer in Excel, to record how long things took to happen on a model railroad. You can use the same code to time anything of course, like how long your code is taking to execute.

I'm going to use the Timer function which gives us the number of seconds elapsed since midnight.

Knowing that we're basing our time calculation on the number of seconds since midnight, we don't want to go past midnight whilst we're timing something or our calculations will be wrong.

If you want to start timing something before midnight, and finish timing it after midnight, you could use NOW to work out time elapsed.

Timing Algorithm

The algorithm for our code is:

    Save StartTime

    While Stop button hasn't been pressed:
        Check elapsed time
        Display elapsed time on sheet (CurrentTime - StartTime)
        Display elapsed time on status bar

    When Stop button is pressed:
        Exit code

We can implement this as two subs. The first does the timing and display to the screen, the second sets a flag to indicate the Stop button has been pressed and the code should end.

These subs will be assigned to shapes so the shapes act as start and stop buttons.

The Stop button just sets a value in a cell. The timing sub monitors this cell until it sees the value that indicates it's time to stop.

Here's what the code looks like

You'll notice I've actually written a third sub called ResetTimer which just resets cell A1 to 0:00:00. This isn't really needed so you can remove it if you want.

Enter your email address below to download the Excel workbook with the timer code.

Get Excel Workbook
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.
Excel Workbook Timer.Stopwatch.xlsm

Here's what it looks like in action

I've included code that displays the elapsed time in the status bar as the timer is running

   Application.StatusBar = ElapsedTime

When the timer is stopped the elapsed time is removed from the status bar

   Application.StatusBar = False

If you don't want to see the time on the status bar just remove these lines.

DoEvents

One thing we must do with code like this is use DoEvents.

When we call DoEvents it allows Excel to do other things, like check if the Stop button has been pressed.

If we just kept looping around displaying the elapsed time, Excel would hang.

Displaying the Elapsed Time

I'm using the format function to display the time as hh:mm:ss.

There are many formats you can use to display numbers, dates, times, serial numbers and strings, so read up on what the function can do.

NOTE: We have an in-depth guide if you want to learn about custom number formats.

The post Timer or Stopwatch in Excel VBA appeared first on My Online Training Hub.

Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview