How to run a data query

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

Okay. Let's get started with the Currency Converter Project.

The first thing I'm going to show you is how you can run a query in Microsoft Excel,

and how you can import information directly from the internet into Excel.

Now, I haven't had the best of luck with using

these queries in applications where you have to automate things.

So, I've provided on the website some code that I've been able to find and adapt,

that does a really good job of importing data

using these queries so that you can use in your project.

The data that we're going to get for our currency is found on this website here.

You're welcome to use other websites that have different currencies,

but this is kind of a nice one to use.

This is the website, and you can select

down here the unit that you want to use kind of as a base,

or a comparison, and then you can select the day.

The nice thing about this website is we can go back in time to see what

the historical rates were at that time period.

That's going to be important in our user forum when we select the last 30 days.

And then we can go ahead and click go,

and it goes and finds the data.

So, as of today, these are the currency exchange rates.

Now, all of these are based upon one US Dollar,

but you can still use this table to do any of the other conversions,

for example; a Swiss Franc to a Japanese Yen.

You can just base those on the left column here by performing different ratios.

And I'll explain how to do that here in a few minutes.

I want to show you how you can do a query.

You can go into data tab here,

and if you've got information on the web.

So, for example, here I'm going to go ahead and just copy the website,

and back over here on our spreadsheet,

we can go into from web and I can put in the URL.

I can just paste,

and then I can click okay.

And what it does is it accesses the web,

and it's bringing in a query table.

And when this box pops up,

I'm just going to select table zero,

and you see that this is sort of a format that we're going to import,

and then I can click load down here,

and it goes through,

and it imports the information from the web.

So, here it is and this is our query.

Now, if you want,

you can try to use this.

I haven't had the best of luck using this type of a query.

Let's go into the developer tab here.

We can sort of hover over table zero over here.

And you can see what it is referencing,

and it's kind of linked to the data.

So, you can save this file and the next time you open it, it'll refresh.

You can also just refresh these queries by going up here to the refresh tab.

You can try to use this,

again I haven't had very good luck with automating this

especially with using user forms and this information.

So, what I'm going to show you is an alternative,

and I would encourage you to try to use this if you're

having a hard time automating the queries,

or you might just want to start with this approach.

I've been able to find some good code on the internet,

and adapted a little bit,

and I've got this file that's provided called Query - STARTER.xlsm.

If we go ahead and open up the code it's got kind of this With statement,

and it's got some code here.

Again, I got it from most of this from the stack overflow site.

It very nicely allows a refresh,

and it can adapt to a lot of different types of data.

So, I've got this Sub called QueryStarter.

What you going to want to do is,

here where we have the web address you can place your web address there.

And I just kind of put just a default space holder web address right there.

So, let's go to a website.

I've got just a weather website here and you see that we have data in a table form.

So, I'm going to go ahead and copy the web address,

and then back here in the subroutine,

I'm just going to replace.

Now you're not replacing the URL and semicolon,

you're just replacing the URL here.

So, I'm going to paste that, that URL was pretty long,

but I've pasted that in there.

And now when I run this Sub,

so I'm just going to run it from here.

I'm going to do Macros.

I'm going to run my QueryStarter Sub,

and it's going to go through and import the information from that website.

And If I go to Sheet1,

you see that it's imported all that information.

So, that kind of aligns with this table here on the website.

So we import it. It was a very quick import.

By the way much quicker than what I've been able to

get using that Query tool in the Data tab.

So, I've got the information.

So, if I wanted to output automatically the current temperature,

or something, or the dew point,

or the humidity, I could use this spreadsheet incorporate it into a user form,

or and, or a subroutine.

So, let's go ahead and do this for our currency.

You notice here at the end of the URL,

we have today's date.

And that's going to be very important when you're trying

to access days other than today's date.

So, if I wanted to go back in time to January 30 I could change this to January 30.

So, that's going to be how you're going to customize your data to the historic dates,

if you wish to go back in time to get those.

You're going to update this URL.

So, I'm just going to go ahead just to show you how this works. I'm going to copy.

I'm going to go back to our code here,

and instead of that website I'm going to paste the currency information the URL for that.

And now I'm just going to go ahead run this.

Before I run this,

I have to make sure I clear Sheet1.

This isn't actually creating a new sheet,

it's overwriting the data that's already on sheet one.

So, I just deleted the contents of Sheet1.

And let's go back, and now I'm just going to run this using the green arrow up here.

It's accessing that website it already did that.

It's pretty fast and this is the result.

So, it puts the data here.

There's a lot of this header information,

but if you scroll down its got today's information.

Usually the USD starts on line 99.

So, there's occasions like today where it doesn't quite start on line 99.

So, you're going to have to somehow detect where USD

starts and that's going to represent then the start of your information.

So, that's how you can import stuff.

And I would encourage you to use the code that I provided.

One last thing I want to show you,

is how we can calculate the converted value using these conversion factors.

So, here's just a screenshot of the table.

Now, if you wanted to convert US Dollars,

so for example, 10 US Dollars to Euros.

The converted value in Euros would be,

you take your convert from quantity which is 10,

and then we're going to multiply by the ratio of

what you want to convert to which is 0.813,

that's Euros divided by the unit's per current value.

So, the 10 here that's in US Dollars so we divide by one.

Now, if you wanted to convert something other than US Dollars for example,

the British Pound to Australian Dollars,

you take the convert from which is five.

You multiply that by

the column C value here of what you want to convert to which is 1.275,

and then you divide by the column C value here for what you're converting from.

And in this case that is 0.716 for British Pound.

So, that's how you can do the mathematical equation to make these types of conversions.

So, hopefully that gives you a better idea of how you can import information into Excel,

and this will be sort of the foundation for your Currency Conversion Project.

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