Importing data from the currency website using proper date format

Loading...

En provenance du cours de University of Colorado Boulder

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

22 notes

University of Colorado Boulder

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

22 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

After you have initialized the user form with the combo box values,

you are going to have two combo boxes,

one that's going to be a convert from unit and one that's going to be that convert to.

And you're not going to have obviously these values here,

but you're going to have a bunch of three letter currency codes

here with their expanded names here in column B.

So your user form obviously is going to be more complicated than this.

Once you run this,

it's going to populate with today's date as I showed in the previous screen cast.

The next step is to then,

import the data from the currency website based upon the date that is in this box.

It doesn't have to be today's date,

today's date is just the default.

The user could change that if they wanted to.

So, again I would recommend using

this www.xe.com and the way this works is when you run it,

It's going can look up the date.

The important thing is up here in the URL.

This is today's date and it's a little bit different format

than the date that is going to be placed into the date box here.

So we're going to talk in the screen cast about how you can convert

this date into something that's usable in this website.

The first thing we're going to have to do is obviously separate the day,

the month and the year into separate streams because this is

different format from the format that

these inputs were provided here by the now function.

So let me show you some hints on how to go about doing that.

This is going to occur when the import button is clicked.

So we're going to go and double click on the import button and this is

the code for what's going to go when that button is clicked.

The first thing I've done is I've dimmed the date day as string date month and date year.

So we're going to basically separate

the date that is in this date box into three components,

the day, the month and the year.

I'm going back to some of the things that I taught in

part two of the course, in particular,

week two of part two where we talk about

string functions and how to separate dates into their component parts.

Now, I'm not going to show you everything here because this is your project and you

need to fill in a lot of the details

but I'm going to show you how to do this with the day.

I'm dimming this variable called first slash as an integer and I've

defined first slash as I'm using the in-string function of date box,

remember date box is the date,

the text field that's going to be here.

And I'm searching through date box for the forward slash,

so we're going to find then the first slash and it's going to give me the position.

And for February, that's going to be two but for a month that has two digits,

like October or November or December,

this is going to be three so that's going to be an important consideration.

And then, what I'm going to define is date month that's

going to be the left using the left function of

the date box and we're going to take the left most first slash minus one character.

So, for finding the slash in the second character,

we're going to subtract one or take the left most

one of that for February and that's going to be defined the date month.

So, let me go ahead and put

a break point there and I'm going to go into my initial module.

I'm going to populate the form and I've got the date there.

I'm going to click imports and we go through and the first slash you see,

because today's date is February 25th,

the first slash is found at item two.

And now if I run the next line,

we're going to do two minus one which is one,

we're going to take the left most single digits of

the date box and if I look down here in the locals window,

the date month is February which makes sense.

So, you're going to have to do the same thing for

the date day and the date year

and is going to be a little bit more complicated than what I've just did.

If you're having a hard time,

you can go back into part two of the course,

week two and you can look into using the in-string function.

Ultimately, what we're trying to do then,

is to use our subroutine to create a date in this format,

so year, month, day.

You notice that if the month is only a single digit,

there's got to be a zero in front of there so we're going to have to take care of that.

So, let's do that first.

To convert a single digit month into a two digit month like zero two,

I'm just going to add the following code,

if the length of date month equals one,

so if it's only a single character,

then the new date month I'm going to concatenate zero with the date month and actually,

I think I need some quotations around this.

So, let's put a break point here, then we go back.

We're going to run this by the way it's not populating

the combo box because I erased the values in columns A and B,

so that's why we don't see anything here.

Going to click import. Now the length of date month,

if I press f8, is one.

So, I'm going to replace the date month with zero two.

So, you see down here in the locals window that we've got eight month is now zero two.

And that's important because now on the web site,

I've got it in terms of zero two.

If you were to just put two in there and if I press enter,

It actually doesn't recognize that,

it says please select the date in there,

it doesn't recognize that at all so we have to make sure that there is a zero in there.

So again, you're going to have to come up with the date day and the date year.

So I'm just going to put in some values here,

date day is equal to 25,

date year is equal to 2018.

Now, ultimately what we're trying to do is we're trying to use the starter code that I

gave you so I gave you all of this in a query starter file.

We're trying to use the dates in this URL.

So, I'm going to just go ahead and copy all of this,

just going to copy that and then bring it back over

to our user form one in our import module.

I'm going to put this all sort of below what we were just working on.

I'm going to move this URL to higher.

It's not necessary but it's quite nice to put all the dim statements at the top.

We need to replace this parts with the website we're trying to access.

So, I'm just going to do some concatenation here,

I'm going to close the first quotations,

I'm going to concatenate that,

go back to the website here and I'm just going to pluck off all of it up until the date.

So, all of that through the equal sign,

I'm going to do copy and then I'm going

to paste that here and I could actually let me just put it here.

I don't need that concatenation there.

So, I'm going to put a quotation.

I'm going to concatenate that and then this is where we need to concatenate with the year

and we need to put a hyphen and remember we're going to need a

zero there but it's year, month, day.

So, I have concatenated it with the year,

with hyphen and the date month and hyphen and the date day.

So that for today,

two 25 2018 will be equal to what I've got currently in here.

So, I've added back a few things here the original columns that I had.

Some of you, probably were following in original screen cast of mine where I

had the values in columns A and B.

The subroutine that I gave you is working with sheet one here,

that's where it's going to import the data starting in range A one of worksheet one.

So, if you've got these labels here that you want to add to your combo box,

you need to make sure to rename this so I'm

just going to rename this something like names.

Now, I'm going to add a sheet one and I just need to make sure rename

this to sheet one because again our subroutine here,

this query 17 subroutine that I gave you is using sheet one,

is looking for sheet one in range one to import the data.

So, now we're ready to import the data,

I'm going to go ahead and start from the open forms up,

I'm just going to press f5 and we go through

and it's not recognizing this because I forgot to change the name,

I forgot to activate the names sheet before I populated that combo box,

but that's something that you can figure out.

But the important thing is taking this date here and

extracting the month, day and the year,

put it into the format that's required by

this website which I just show you and then we're going to import the data.

So, let me press imports and it goes through

and it took the data from our website for two 25,

then we press enter here,

so it took these data that our real time for

today and imported it into this spreadsheet into sheet one.

Then what you're going to want to do is you're going to want

to detect where in column A you start with the code.

So, what I did was I just search through until I found

USD and that is my new active cell.

And from there then,

I can use the conversion formula depending on the boxes that the user selects.

We can active cell offset from there to get the different values

required to do the conversion and then you can output that into the form.

So there's a lot that I'm not going to show you,

but you can ultimately convert something like this,

just the basics that I have showed you,

into something more sophisticated like the ones shown here, where we've got the date,

we've got the convert from, the convert to,

the amounts, the converted amount and then we got a quit button.

So, that's sort of the foundations of the currency converter project.

The one last thing you are going to need to do which

requires a little bit more work is to make

a plots of the last 30 days and that's

what I'm going to show you in the following screen cast.

Thanks for watching and 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