Plotting the last 30 days

Loading...

En provenance du cours de University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 3 (Projects)

9 notes

University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 3 (Projects)

9 notes

Cours 3 sur 3 dans Specialization Excel/VBA for Creative Problem Solving

In this course, learners will complete several VBA projects. It is highly recommended that learners first take "Excel/VBA for Creative Problem Solving, Part 1" and "Excel/VBA for Creative Problem Solving, Part 2". This course builds off of skills learned in those two courses. This is a project-based course. Therefore, the projects are quite open-ended and there are multiple ways to solve the problems. Through the use of Peer Review, other learners will grade learners' projects based on a grading rubric.

À partir de la leçon

Easy Projects

Choose one of two potential easy projects to complete!

Introduction to the currency converter project4:58

How to run a data query8:33

Initializing the combo boxes and today's date8:09

Importing data from the currency website using proper date format10:33

Plotting the last 30 days10:03

Rencontrer les enseignants

Charlie Nuttelman
Instructor
Chemical and Biological Engineering

For the last part of the currency converter project,

you're going to have to plot the last 30 days up to,

and including, the date that is in the date box here.

So what you're going to need to do is,

it's going to have to convert.

So, if I put a one there in US dollars to euros,

I do the plot last 30 days,

It's going to do the conversion for

one to whatever you're putting here for the last 30 days.

And this is what you would end up with.

You would end up with a plot.

I'm going to talk a little bit more about how you can make this plots in this screencast.

The first thing you want to do is,

you want to create a hidden sheet that you're going to have to hide

and unhide at the beginning of each time that user form is used.

What I would recommend is in cell A30,

that's where you're going to put the date that is in the date box of the user form.

I'm going to write code that will select Range("A30"),

and then we're going to use A4 loop iterating backwards.

And we're going to use the DateAdd function in

VBA to add dates here and then iterate backwards to cell A1.

First thing I'm going to do is, select Sheet1.

It could be something else.

It's probably going to be named something else in your actual sub.

I guess I'll go ahead and show you this too.

Let's say I had another sheet two,

and you're probably going to want to hide this sheet.

So you can just hide it.

And this is how you would start your subroutine.

This is how you start. Right now, it's hidden.

But if you want to unhide that,

you can put in this command here, ("Sheet1").Visible = True.

So, now it will show it.

Then, you're going to select Sheet1.

We're going to select Range("A30").

So we are going to start at cell A30.

In your project, you're going to get this from the user form.

But I'm going to dim a variable today's date.

So I got today date dimmed as a string,

and I'm just going to obtain that in the InputBox.

So the user will input that in the InputBox.

Again, you're going to get that directly from your user form.

Starting in cell A30 that we've already selected here,

I'm going to iterate backwards in A4 next loop.

I need to dim i as an integer,

but I'm going from 30 to one in steps of negative one.

And what I'm doing inside of each of the iterations is,

I'm setting Range ("A" and 30 minus i plus one).

So, if i equals 30 at the beginning,

then what I'll be doing is subtracting 30 from 30,

which is zero, and doing plus one.

So, Range("A1") is going to be a DateAdd.

I'm using the DateAdd function in VBA.

If you put a D here,

it's going to start with today's date and is going to

offset that by the number of days that is in the second argument.

So, today is two 25, 2018.

So, if I put in a negative five for example here,

and that's the day argument because I have D here,

so it's going to subtract fives days from today.

Basically, we're offsetting, where we're starting with today's date,

and we're going backwards in time.

And today's date, ultimately be placed into Range("A30").

Again, if i equals 30,

in Range("A"), this will be 30 minus 30 plus one.

In Range("A1"), we're going to do DateAdd.

Now, today's date, we're going to offset by negative 30 plus one,

which is 29 days.

So, 29 days ago will be placed into cell A1.

And then today, if i is equal to one,

then 30 minus one plus one is Range("A30"),

and we're going to offset the negative one plus one and zero from today's date.

If I wanted to, I could have also just use the now function here and extracted the date,

but I want to just do this in general so we can go back and do

30 days from any days that the user selects.

I'm going to go ahead put a breakpoint here.

And let's go ahead and run this,

enter today's date, and press okay.

And then, now we're going to go through.

Let me put this side-by-side with the spreadsheet.

You see that I have selected Range("A30").

I've already input today's date.

And now we're going to enter into this for loop by pressing F8.

So I've done a couple of those.

If I scroll up here,

you'll see that I've already placed two of those dates in there.

And as I keep going,

you see that I place consecutive dates.

So this is going to go all the way.

Let me just hold that down.

It's going to go all the way up until

today because that's when i equals one. And then we're done.

That's how you can populate column A with the dates of the last 30 days.

And if I wanted to do a different date, for example,

if I want to go back in time, so,

January 23rd of maybe 1999,

then I can press okay,

and it just went through and populate that with the 30 days up to,

and including, the date that I just entered.

So this is going to be valid for any date that the user specifies.

What we're trying to do is to plot the exchange rates,

the conversion, and you're going to then enter into a for loop.

This is probably one of the more complicated things of the project,

but you've already got everything in place.

You're just going to use the dates in column A.

Those are going to go into one of the subroutines that you already created just

the converting for a single value,

but you're going to go through and just do a for loop of each of these dates.

You're going to look up. You are going to import the data,

and then you're going to do the conversion.

You're going to output the result to cell B1.

So I've just done this.

I've just taken some data from my currency converter project,

and I want to show you how we can generate a plot with this.

Now, a lot of times when you plot,

you can just select the two columns,

Control+Shift+Down, then I go insert chart.

So I'm just going to do a nice line plot here,

and we get a nice plot of the date and the exchange rate there, the conversion.

I'm going ahead and delete this.

Another thing we can do is,

I can change location,

and I think you should probably do this in your project.

We are going to put this to a new sheet,

something like last 30 days,

and it's going to appear as a new sheet.

Sometimes, and I want to point this out because this

is probably going to be important for a lot of you,

I've run into problems with plotting dates in Excel.

And if that's the case,

I want to kind of show you a workaround.

It's working for me right now,

but I have run into situations where it doesn't nicely plot the date.

Instead, it will plot something that doesn't make sense.

Sometimes, you might see like the year 1900 down here instead of 2018,

which is what you want.

If that's the case, if you having a hard time plotting the dates,

then I would recommend doing the following.

First, just put A in another column here,

just put a reference formula equals A1,

and then you're going to press enter,

and you're going to drag this down for those 30 days.

And again, it's working fine for me.

But just if it's not working for you,

this is something that works well.

And then, what you're going to do, you're going to click on

column D and go up here to the date box and do text.

And what that does is,

it convert it to basically an integer value that corresponds to the day.

Then what you'll do is,

you go into your plot,

and you can right-click on select data.

And instead, you can edit this.

And instead of the x-axis value being column A,

you can delete that,

and you can just select D1 to D30.

Let's go ahead and press okay.

When you look at the plot, It's got the integer value there, which you don't want,

but you can always then go here and right-click on the axis, format axis.

And on the numbers down here,

you can change this to date.

And when you do that, it'll convert it to a nice date.

So it will take that integer value and it will convert it to a date.

And so, this works well.

The last thing you want to do is,

you don't want this to be shown to the user.

All you really want to show them is the plot at the end,

plus maybe a sheet that has the conversion factors at the beginning.

So, in your code,

you can just put in something to hide Sheet1.

And so, I'm just going to copy this,

which we used earlier,

and I'm going to press paste.

And instead of true, I'm going to do false.

So now when I run this,

we start with our sheet that already has the plot on there.

But when I run this,

and let me go ahead and hide this sheet,

when I run this subroutine,

it's going to ask me for today's date,

and I'm going to put in something like two,

let's just do a couple days ago,

and I'm going to press okay.

It's going to go through there and then it's going to hide the sheet.

But what it did was,

it used different data here to plot it.

It started with a couple days ago.

One last thing I'd like to do,

you notice that when it went through that,

you could actually see the Sheet1 after it became visible.

I'm just going to put a line here,

Application.ScreenUpdating = False,

so that you don't see what's going on behind the scenes.

So, hopefully, this screencast gives you a better idea of how to plot

the last 30 days from a user-defined date. Good luck.

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2018 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play