Hey, good.

morning, good afternoon, good evening, potentially, and welcome to this Felix live session on Excel charts and graphs.

My name is Thomas Krause.

I'm head of financial products here at Financial Edge, and I have the honour to guide you through this introductory half hour session today. Let's have a quick look at what's on the agenda today.

So we're going to start with an overall introduction to charts.

We're going to discuss as to what they might be used for.

Then we're going to see how to create a chart in Excel.

We're then going to see how we can add certain chart elements like, for example, legends, et cetera.

we will also introduce how we can do some formatting, uh, on charts, for example, adapting colors to color schemes, et cetera, et cetera.

And then also we are going to discuss how we can work with a secondary axis, which is really, really helpful, especially in finance, where we often want to compare different data series or we want to just visualize the link between two sets of data.

And then towards the end, we will also very briefly show how we can ask Excel to add a trend line into a chart.

However, it's also important, and we're going to talk you through this, what this Excel trend line actually is. However, before we start, a couple of general reminders.

If you want to, uh, try out your new skills after the session has ended, then you can access an Excel document with a couple of practical, uh, workouts on the Felix Life website of this session.

Then you can go there by just going directly to the Felix Live website.

So Felix FE training.

Then in the top left corner, you select the topic, Felix live from the dropdown list, and then you scroll down to the end of the page until you find the, uh, tile with today's topic on it.

Click on it. And that brings you to the website for this particular session.

And there you will find the Excel file and its solution file, uh, ready to download.

But without further ado, let's get started.

And the first slide that I'm sharing here with you is basically just telling us how in general we can create a chart in Excel. There's a couple of keyboard shortcuts here.

I'll leave it up to you if you want to remember them or not.

But we're going to talk a little bit about this. We're going to see an example.

I'll walk you through, uh, a couple of those things.

However, before we actually start doing this, um, I think we should just briefly think about why you might want to add a chart in an Excel document in the first place.

And of course, we all know the answer.

It's basically about visualizing, um, data.

And this is of course, especially important when you have really large, uh, data sets because there, adding a chart can really make patterns and other pieces of information that otherwise will be hidden in this vast amount of data.

Much, much simpler, uh, to see.

And for this, let's have a look at a practical example.

So what you can see on your screen now is, um, a, a series of market prices, um, that is to be precise, the six months euro dollar FX basis.

That is basically, um, a number that's of course very important for all those folks that are linked or that are involved in the FX derivatives market.

It's not really that important what exactly it is today.

It's just merely, uh, an example.

Um, but in case you wanna learn more about it, I recommend the, uh, playlist FX swaps and cross currency swaps that is available on Felix, um, for you as well.

But what this is here is an almost 13 year history of daily prices of this basis, end of day.

Uh, and so that means because we have about 250 business days a year just round, you know, making my life easy here, we have almost 4,000, uh, data values here over that 13 year history.

And of course, if you want to learn a little bit about how this basis has behaved over this, um, time from 2008 to 2021, of course you can scroll down, right? You can do it fast, you can do it slow, whatever you want.

And you see here, obviously, um, yes, numbers will change.

Um, so there is some movement, but it's going to be pretty difficult to really recognize a pattern here, I suppose.

So what are we going to do? We're going to create a chart and, um, that brings us back to our PowerPoint document.

So I bring that back up because there's some valuable information on this, uh, slide here to cover.

The first thing, and this is where it's, so, um, large letters here at the beginning of the document.

Um, it's recommended to select the data that you actually want to convert into a chart before you create the chart.

So select data first and then apply those, uh, keyboard shortcuts and, uh, you know, very, or basically the main reason is it's more efficient to do so.

For example, if you want to take advantage of Excel's recommendations.

And one thing that you can see here is if you use alt nr, that opens a window in which Excel recommends a couple of different chart types for you.

And this recommendation will be based on the data that you have selected.

If nothing is selected, then of course how coulda come up with a meaningful, um, recommendation.

Also, quite useful if you have a header.

So if you have a name for this data series, select this as well that will then be used, um, as a name of that data series.

If you don't select it, then it will be series one, series two, series three.

That, of course, is something we can fix later on, or we can just, you know, um, not fix it at all if we're not interested for some reason.

Um, but of course, if you're planning to have meaningful names attached to your data series, then of course that would be, uh, another thing, uh, to consider.

So as you can see here, alt, and that basically is the shortcut that you probably want to remember.

And then after that, there comes another letter or even, uh, two, um, letters or a letter and the number that will then get you into the more detail. We're going to see it in Excel in a minute, all the F1, right? Just to kind of mention that as well here at the beginning is basically telling Excel, create a chart, um, based on your choice.

And that of course is in a way maybe the quickest, uh, way to create a chart.

You just might not get the chart type that you want, but of course you can change it, uh, later as well.

So let's go and have a look at how this chart, uh, functions would look like.

So the first thing, as I said, is we're going to select, uh, the data here.

So it shows you we're going to roll 3,915.

So I was right with my approximately 4,000 there.

And now what we're going to do is we're going to press old, and then we see obviously how N is for insert, and that's where the chart functionality lies. However, before we do this, let's start with old F1 and see what, uh, gets spit out.

And this is then the result, old F1, and we get a chart that actually looks, you know, quite reasonable. There's a couple of things I would like to change personally, though.

I don't really enjoy this field surfaces that much.

I'm a bit old school.

I prefer my simple line chart, so I would like to change that.

Um, but you know, that is as a, you know, two key strokes, uh, not a bad outcome.

And this is certainly something that one could work with, um, very, very quickly.

So, but let's get rid of this just to show you a couple of these other shortcuts and what the result is.

So old N and then R for recommend. Whoops, apologies, old N.

And then the R for recommendation.

Here we see a couple of, uh, recommendations off xl.

The first one is a scatter chart.

IE you know, the, the, the, the dots here, which I don't think works that well.

So I wouldn't, um, use this, uh, here is what I personally prefer.

I said that the line chart, that's just something I'm used to.

Um, but of course, you know, don't, um, be stopped by using something that's more creative or that's more appealing to your eye.

Um, but you know why? It's also interesting, um, to click on the recommendations even though you have in mind what you want to use, Which chart type is every now and then you get some sort of, you know, interesting idea. And that what happened to me, uh, when I, um, sort of did this, um, this morning for the first time in a long time to use the recommendation feature.

Um, I saw this one here. Uh, and that's a histogram, right? So that's basically then showing us not how the data has behaved over time, but it shows us the frequency of certain values.

It's really not what I had in mind when I wanted to create this chart, but it's really, really something that I've been, uh, doing with in other contexts, um, quite a few times in knowing that this function is now available in Excel. That obviously, uh, was a great find.

So that's going to make my life much easier.

I'm not sure you will benefit from that, but I still wanted to share this, um, with you.

So that's the recommendation.

Now, of course, um, you know, on the, if you remember the, um, the, the PowerPoint document, there was all NK that gave you to all charts.

Um, but you know, obviously you don't have to do this, you can just click on the tab, all charts here.

And then what you see is that here is sort of itemized by different chart types, a lot of different, uh, types of charts that you can uh, use.

Not all of them will make sense, right? So a pie chart, for example, here for 4,000 values, what's the point doing this? They will all have the same, um, you know, waiting, I would say.

So this is clearly not something that works here, but in other circumstances, of course it will be the right choice.

So you can basically go down and try out things. Here you see a little bit of a preview how these things will look like at the top.

Um, but that's very stylized.

It doesn't include the data set, but you have the preview here.

If you hover over it, it gets, uh, a little bit, um, magnified as well.

So this is certainly a good thing, uh, to remember.

And then there's a lot of other shortcuts, but, you know, basically alt n that gives you the chart, um, or highlights, the insert menu point.

And then here, obviously you see all the different, uh, options that you can select.

So C one is basically, okay, I want bar or column charts.

NN one, uh, is then basically giving us here the line charts.

We have the recommendation, uh, with R as we've seen K for all the charts, E, et cetera, et cetera. So, um, no need to remember all the shortcuts or just, uh, use mouse anyway, okay, so let's do this.

And let's say we want to use a line chart.

So we're using, uh, this one here, and there we go.

We have created a line chart now.

So this shows us the six months euro dollar FX basis.

Over time, it looks great.

It certainly kind of, you know, shows us that there were some interesting patterns. So this one obviously looks interesting, this one as well.

Um, and, um, you know, certainly we, now, if we were going to analyze this, uh, knowing had a bit of an idea where to look and where to sort of dig in with our research, but I, I think overall this slide still looks a little bit, I don't wanna say poor, but it doesn't look super great, right? The couple of things that you could argue are missing, right? First of all, if I see this for the first time and I don't know anything about the FX based, then I'm going to ask, okay, what is this? What's the y axis, right? What's the, what's the unit there? Is this percent, is it basis points? Is this dollars? What is this? Right? So that needs obviously some further information.

Then here, the x axis, there's no dates, this is just numbers, right? But we want to obviously, um, we have the data column here available in column A.

So we might wanna have the dates as the values on this axis.

And then also, and this is another point, and again, this is maybe personal preference, but I find it quite, uh, disturbing that the numbers are overlapping with the chart.

So I would like to change that, right? So this is a couple of things, uh, we can do.

One thing that we do not have to do anymore is change the slide title.

And that's been chosen automatically simply because we have one data series, right? And we have selected the header here, six months, euro dollar, us, um, or US dollar FX basis. However, if you wanted to change this, and maybe we just call it this, uh, put test here to, um, to show this, what we could do is just click on the chart title, right, and then link it to this cell, and then we have a cell which we can, uh, now change, and the chart chart title will update automatically.

So this is something, uh, that you might wanna keep in mind just, um, for sake of efficiency, okay? So, uh, this is good, but now as I said, we wanna make a couple of changes.

And I'm going back to, um, the, um, PowerPoint document now, which hopefully you will see.

Yes, there it is. Um, because now what we want to do is we want to start adding chart elements, as I said, the slight title is, or the chart title is already there.

If it wasn't, we could simply edit, right? And the general shortcut for, um, starting to add chart items is you first have to select the chart.

So the chart needs to be selected.

We cannot just open a spreadsheet that has a chart in it and then expect it to work. We have to select the chart first, and then we have to press old J, c, and A.

And that gives us then the menu point of the, uh, chart elements.

So I think we can go back to Excel now, um, and start, um, doing just that.

So let's go and select the chart, then we do old j, c, and A.

And you see, obviously JC is for chart design.

And if we press a, it gives us the, um, you know, down for chart elements.

Now we have the title, but if you wanted to change it, you could do here, it could be, um, you can think a little bit about positioning, although of course you can also manually move this around if you want to, right? So push this over here, um, if you don't want it in the middle, whatever your preference is or works best from your layout perspective.

Anyway, let's go back to the chart elements and said we wanted to have an access title, right? Maybe not so much on the, on the horizontal, because that hopefully once we changed the data to, um, showing the actual date, um, will be pretty much self-explanatory.

But we want to, um, add, obviously here a vertical, um, or a description to the vertical axis.

And here we just chose, uh, chose this and now can say in basis points.

Um, so that basically now makes clear that these numbers here are actual basis points, which is one basis 0.0, 0.01, um, percent.

So this is now clear.

You can obviously write down a lot more if you wanted to, um, that I'll just kind of put something in here that is half, half, you know, more or less, um, meaningful.

Okay, so what else do we want to do? Um, access title, we have done, we have the chart title.

Maybe we want to include a legend.

Now of course, in this particular case, maybe adding a legend is not really super important because we have one data series, and that is, uh, then also it used to be before I made the change, uh, obviously, um, the chart title used to be linked to, um, the data series name.

But let's just still think you want to have a legend.

You again, can google the whole shortcut thing, or you just click on it like I just did.

And then you go to legend, you can, uh, put the legend to the right, to the top, to the left, to the bottom, or wherever you want it to be.

Uh, and now of course you can see, uh, the legend here at the bottom of the slide. And of course, once you have more than one data series, legend becomes incredibly, uh, important.

So this is, um, pretty much all that we wanted in terms of adding, but while we are already on this chart, um, I think, you know, what we, what we should be doing is we should start, um, formatting it.

Um, and not just the chart, but also maybe the access, because this is what, um, we have, uh, talked about, um, just a couple of minutes ago.

Okay? So how do we do this? Let's go back to PowerPoint.

Um, and this time we're going to talk about formatting charts.

And, um, if you want to use keyboard shortcuts, then what you have to do is, again, you have to select the chart and then control one that what opens the general chart formatting, uh, menu.

And in there you will find a lot of, uh, different items.

So once again, we go back to Excel and see how this would look like in practice.

So now we select the chart control one, bam, there, see, uh, format chart area, um, option. And then there's obviously a couple of things that are, um, suggested to you here.

We can start with general chart options.

So you can sort of choose a fill color here if you want it for whatever reason, uh, the chart, well, the whole, uh, chart to be filled in a light gray, for example.

You can do this. And then the whole background is, um, field, you can add transparency, et cetera.

You might want to change, um, the color of the text, just again, you know, where we can obviously, uh, change and adjust things to color schemes of, um, your company or whatever layout you are, you are working.

You have the text outlines things that you know, um, from office products in general.

Um, but then of course you can also select the dropdown area here or the dropdown arrow and get access to other pieces that you can, uh, format, not just the chart area.

You have the chart title here, for example.

Not that this is, uh, necessarily something that we, um, need to focus on all that much, but you know, we have the horizontal category axis.

So that is one where I said we might wanna, you know, work a little bit on that, right? So here we can see, okay, for example, the, um, access options, right? Um, we go there and, um, what we want to do, first of all, is to bring the labels away, um, from the chart, right? Because right now the numbers are overlapping our chart.

And that's because, um, you know, standard setting seems to be that the horizontal, um, labels will be at around the zero line, right? So that's the first thing we need to do.

And we go for this, we go to labels, um, and then we are looking at the label position here, which right now is next to axis, and we changed this too low.

And now what we can see is the whole thing has been shifted down.

So now we have clear visibility of our chart, and we can also read the numbers.

And that's a great reminder that we wanted to do something else, right? We wanted to change those simple running numbers to actual, uh, data points.

Now, this is nothing with formatting. We have to actually go and change the data or select the data for the chart.

So this is in the chart design point as well.

Here you see, select the data, but if you're looking for a shortcut, right? We can go JC and e and there's the, uh, shortcut for this.

And now what we want is not to change the, um, date range for the series, but we want to actually change the range of the, um, data used for basically labeling the xxs.

So we select now the dates we press okay twice and we have to scroll up now.

And there you go.

You can see this has now been changed to dates, and now the chart starts looking like something that is, um, telling.

And now of course we can change the field, we can, um, decide to have maybe a, um, um, a finer grid to just be able to read numbers better, et cetera, et cetera.

But that is, um, certainly something, um, that we can play around with and do change the chart pretty much to our liking.

So, um, now the one thing is missing if I look at this and say, okay, how about we want to maybe comment on specific points, or we want to, um, highlight certain things within the chart, that's then something where we might wanna, um, basically add, um, shapes, right? So how can we do this? Let's go back.

We again have to select the chart, then it's all JA and that gets us then into a position where we can insert shape.

So old Ja, remember that one, I'm going to do it now in our chart here, old Ja a and there we go.

I can, uh, decide to put a shape in and let's say I want to put some, uh, box in here.

I don't want this to be filled that, that's just formatting the box after I've edited.

And now let's say I wanna put some text inside and say, okay, GFC, now I have to change that text color to, um, black.

Um, whoops, that is the filling. Apologies, no fill.

Um, and now we have the text color, and that is the text that I wanted to add because this outlier of data here, if you wish, that basically was because of the global financial crisis.

Now, one beauty of this, once you added a pattern into the chart, um, what you will see, and I'm going to move the chart around now, is it's been basically grouped with the chart.

So no matter where you move the chart, the position of this, um, shape relatively to the chart won't change.

We can do the same, obviously for this one here, I can do this, but in order to save some time, I'm not going to put it, but I know you want to know what was going on there.

It was a European sovereign debt crisis just to, um, you know, serve some market education for free here as well.

Okay? So that's the situation.

Now, I think we're really happy with that slide. Of course, you know, you might, uh, question my graphic skills, but generally speaking, um, a lot of things we can, we can do. And I hope you found those tips so far, um, beneficial.

Now, the next thing that we then wanted to do was to introduce how you can actually work with two axis.

Now, there is a, um, slide for that on PowerPoint, but instead of jumping back and forth all the time, I'm just going to talk you through this, right? And the secondary axis is something that we often use, um, if we want to sort of display two, um, sets of data.

And this normally is required when the values of those data series are actually quite different, right? Um, and so let's change the tap here and now, now go to share price and volume, um, which is basically just showing the share price of a specific stock, um, at in one minute intervals, right during the day.

And if you wonder, why is this, you know, three 20, um, the first price, that's because it's 20 minutes delayed feed.

So that was basically three o'clock where the first price applied, and that of course was C3 o'clock central European time, which is in a different time in another, uh, time zone, which then means we have, uh, here, most likely a nine o'clock start at some, uh, place in the world.

But anyway, none of this really matters.

I just wanted to show two time series here.

So what I'm going to do is I'm going to select, uh, those data points here, then we're going to go old JI, yeah, not old, hang on, we're going to old n and then we go to, um, all charts, right? Or recommended charts. Let's see what XO recommends.

Again, a scatter. Um, then this chart title here, um, this doesn't really look that telling.

So we go to all charts and something we very often use in those instances is a combo chart.

And the reason for this is that you can sort of very easily, um, switch the chart type that applies to the different data series, and you can also very easily and conveniently define, um, which, um, series should be on the secondary, um, axis.

So let's have a look at what we have suggested here.

Price is, uh, selected as clustered column.

Now, I'm, uh, not really a big fan of this.

I, for me, the price has to be aligned.

So I'm going to change that and the volume that is a, um, column in my mind.

So we have now told Excel, okay, foot price use line, and for volume use clustered columns.

Now this looks okay, right? We see here some columns, we see a line, but the line isn't really giving us much information, and that's because we compare price and volume.

These numbers are vastly different. So what we're going to do to fix that is we're going to click secondary axis for volume or for price, whichever, um, your, your, your preference.

Uh, and then what we can see has now happened is we have for price, the left, um, axis applies, and then for, uh, volume, the right hand side, uh, applies.

And now we can see the price behavior fairly clearly, and we kind of say, Hey, what happened here between those different points in time, it is basically a, a, a break in exchange trading.

Um, then there was also no volume there.

And we can see here that obviously there was a very large volume at the open is slightly, well, no, uh, high volume at the close in between.

Um, of course it looks like not much was going on, but still these numbers are in the nineties, uh, 90 thousands or et cetera.

So basically we can now say, okay, uh, then we realize, okay, we have no chart title, uh, we could put that in.

We can then obviously change our, um, you know, the, the, uh, the data, um, that applies here.

So why don't we just do this very quickly.

We're going to go to edit and select the timestamps, and then we have here the times.

So that looks already a lot better.

Um, and so that's pretty much all there is to say about the secondary access.

So this is something you might wanna, um, keep in mind works as well with two lines, right? If they are on a different level, so you don't really wanna rebase stock prices, et cetera, you can make them that are comparable, even if they're not, uh, the same, et cetera, et cetera.

And with that, ladies and gentlemen, we have reached our final agenda item, which is, um, and I'm sharing my PowerPoint now for that matter, uh, again, uh, which is the, uh, trend line, right? And, um, as I said, right, yes, we can ask Excel to add trend lines.

However, it's important to understand what exactly a trend line in Excel is.

And you know, if you come from a market background, for you, a trend line is sort of like connecting two or three, um, dots on, on, on a eye chart, right? Um, and then of course, draw a linear line. And that's pretty much the trend, um, line for you. Of course, you've gotta be careful with how you position this and all this kind of thing in Excel. However, a trend line is what, you know, one would describe as a line of best fit IEA line that really visually represents, uh, some general trend of data points on a chart. And plain English what it is, it's a, it's a line that best represents the relationship between, uh, the variables being plotted.

So most often you will use trend lines, for example, with scatter plot charts where you have two data series, but of course you can add them, uh, to chart types like lines and, and, and columns, et cetera as well.

But let's focus on, uh, the scatter plots. And so what you do is, you, you or what, what the trend line then does it, it shows, as I said, the, uh, relationship, um, um, between those two variables being plotted.

Uh, and that of course, even when the individual data points will probably, uh, deviate from the line, it's a best fit, right? It's not perfect fit for every single point, but it does sort of indicate, um, some general trend.

And so one example which, um, I find very interesting, uh, to use this for is, um, when you want to calculate the beta of a stock, for example, now I'm not commenting on how many years of data and if this should be weekly or daily or whatever, but here what I've done is I just kind of pulled, uh, a series of s and p 500 data.

We're using ETFs, so don't get concerned.

This is not where the, uh, index trades right now.

Um, but this is the, uh, s and p 500 ETF closing price.

Then we say, just calculated the closing, uh, the, sorry, the percentage change from one day to the next.

And then we've done the same for Tesla, right? The closing share prices, the percentage change.

And now what we can do is we can simply calculate, um, or well basically first of all, draw a scale chart, right? That will allow us to visualize how Tesla moves relatively to the s and p.

So I've selected the two data series.

Now what I'm going to do is I'm going to insert, and I'm going to, um, add obviously here a skea, um, excuse me, uh, where is it? Um, there it is, uh, a scatter plot chart.

So I click on this one, and now I can see obviously that there is some sort of, you know, there's, there seems to be some sort of, um, relationship between, uh, those two numbers.

So kind of, there's a line here that you could sort of imagine, um, from the bottom left to the top, right? Um, but you know, we don't have to imagine, we can just basically go to Excel, right? Click on the chart, add the trend line, and then of course we can choose between linear and logarithmic and oma.

You can calculate moving averages and all that kind of stuff.

So I would highly recommend if this is something that you can see being useful for you, spend some time, look at it, try it out, what are the different options here.

But basically, you know, you see the line and you're saying, well, that's maybe a little bit thin.

Also, it's a line that I could sort of imagine.

I want to see what is the beat of, um, of, um, Tesla here and what's the r squared value? So you can just basically select these two items here at the bottom and uh, then maybe just shift that up.

And basically what you can see here is that, um, y equals 2.04, let's round a little bit, um, times x, right? And then minus some sort of like small correction factor.

But basically what you can see is that the beta of Tesla for this period on a daily basis was 2.04, meaning s and p goes up 1% as Tesla should, uh, move in the same direction, but 2.04 times as much.

So 2.04 um percent.

Now of course we said it's the best fit.

It doesn't, um, you know, mean that this 2.04 is applicable for every day, right? And this is where the R squared value does. Tell us a little bit on how well, um, this price movement in the s and p actually explains Tesla's price move.

And so here the R squared is 26% or 27% was a bit of rounding, meaning that 27% of the move of Tesla is explainable, um, by the move in s and p 500. That's it. That's a fairly simplistic explanation of this, I know, but you now see what the trend line feature in axle can do for you, how it can be helpful.

So, uh, recommended just do some research if this is something that you can think about being useful for you, uh, as well.

And that's all I wanted to share with you here today. Thank you so much for your participation.

If you have any questions, let me know.

Um, we can still sort of discuss them, but, um, if you don't, then thank you so much.

Have a great Friday. Have a great weekend, and I hope to see you again very, very soon, uh, in one of the similar sessions that we're running here.

Uh, thank you very much. Take care of yourself and, um, see you soon.

Bye-bye.