Solved

Excel Currency Conversion Update

Posted on 2013-11-13
19
292 Views
Last Modified: 2013-11-17
EE Pros.,

Greetings!  I need a little help with a Currency Conversion capability.  I have a SS attached that shows a list of currencies (Dollars, Euros, etc.).  What I'm looking for is a Macro that when selected, would update the currencies by going out to a source (MS Money?) and refreshing the conversion rate.  That's it!

Thank you in advance.......

B.
Currency-Conversionv1.xlsm
0
Comment
Question by:Bright01
  • 11
  • 8
19 Comments
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39644412
While it's possible to do what you want using VBA, it is complicated. A much easier way would be to use Excel's built-in web-query.

Try this, if it is not what you need post back:
1. Select the DATA tab on your excel 2010 ribbon and select EXISITING CONNECTIONS
2. Select the option MSM MONEYCENTRAL INVESTOR CURRENCY RATES
3. Done!
0
 

Author Comment

by:Bright01
ID: 39644434
MacroShadow,

Thanks for the quick post!  

OK... I added MSM MONEYCENTRAL INVESTOR CURRENCY RATES but how do I use it?  Does it post results somewhere?  

I'm looking for a way to automate this and populate the model I attached.  Can I do that with this link?

Thank you,

B.
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39644457
All you have to do is set up the web table once, you get to choose where to save the table. The results will get refreshed automatically.

But, if you are looking to use the model you posted, I guess we will have to go the long way, the VBA way.
0
 

Author Comment

by:Bright01
ID: 39644473
I have no problem checking this out.  How do I set up a Web Table?

B.
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39644489
Follow the instructions I posted, you will be prompted for the location of the table, either use the default, adjust the range or choose "A new worksheet".
0
 

Author Comment

by:Bright01
ID: 39644505
I did follow the instructions.  I selected MSM MONEYCENTRAL INVESTOR CURRENCY RATES. There is no "prompting for a location" when I select.  I'm assuming that gives you a currency table.  How do you get the conversion to happen?

Sorry for the confusion.

B.
0
 

Author Comment

by:Bright01
ID: 39644512
Aahhh.. Ok.... I got it to download the connection.  Let me work with this for a few minutes.

B.
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39644513
Did you click "Open" on the bottom of the window?
0
 

Author Comment

by:Bright01
ID: 39644541
OK... here's my challenge.  It seems like it always loads to the A column and shifts the other columns to the right.  I need it to load where I direct it (B25).  Secondly, I don't know how to add a formula that then changes the currency conversion by selection (drop down box ((K14))) that would change the conversion based on the base currency.   I've attached v2 for you to see what I did in bringing down the Money Connection.

Thank you for the assistance.

B.
Currency-Conversionv2.xlsm
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:Bright01
ID: 39645170
OK...got it to download to the appropriate point.  How do I get this finished up with the "selection" and the population of the results?

I've attached the latest sheet.

Thank you,

B.
Currency-Conversionv3.xlsm
0
 
LVL 26

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39646070
Here is your solution, the rates are from http://www.x-rates.com. I don't know if they're accurate or how often they're updated, check it out yourself.

Disclaimer: I take no responsibility for any damage that may result from the use of this sample.
Currency-Conversionv1.xlsm
0
 

Author Closing Comment

by:Bright01
ID: 39646119
MacroShadow,

Much thanks!  It seems to work great.  I will be adding functionality to it shortly so as to enhance the actual macro to do more.  But you have definitely gotten me started.  And for that I am very grateful.

Thank you again for outstanding work.

B.
0
 

Author Comment

by:Bright01
ID: 39647576
MacroShadow,

Now that I'm working to incorporate this into my major WB, I have one additional question.

I'm not familiar with the constructs where this code resides. I usually use Macros that are either in modules or in the WSs.  Also the names on these objects are not clear.  Can I rename them so as I import them or copy them into my WB, I can quickly identify them as to what they are for?

Thank you,

B.
Currency-Function-Code.pdf
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39647913
Module's names can be changed but don't change the class module's name.

I'm not sure what the gibberish is all about, the first one is the worksheet module and the second one is the workbook module as you can see from the icons.
The code in them isn't critical, it just updates the rates when a. the value of the combobox is changed, and b. when the workbook is opened, respectively.
0
 

Author Comment

by:Bright01
ID: 39648061
Can I consolidate any of the Modules?  or Class Modules?  Can I move the code from the two modules that have the gibberish into a new module with clear naming convention?

Thank you,

b.
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39649029
All standard modules can be combined, just make sure that any declarations are kept at the  top of the module.
DON'T rename the class module.
The modules with the gibberish are not standard modules, as such they should not be renamed by the user. Copy the code from the first one to  the worksheet module that contains the the drop-down list of currencies, and the code from the second one to the workbook module.
0
 

Author Comment

by:Bright01
ID: 39649145
Very sorry; but I don't understand.  I won't rename the class module.  I'd like to get rid of the modules that have the gibberish (never seen that before) and when I try to rename them I get an error message.  I understand your directions on copying the code....but what about renaming the Sheet "Currency" as it was originally?  What gives?

B.
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39649187
You wrote: "Now that I'm working to incorporate this into my major WB"

Copy the code from the first gibberish'ly named module to the worksheet module that contains the the drop-down list of currencies in your major WB, and the code from the second gibberish'ly named module to the workbook module in your major WB.
0
 

Author Comment

by:Bright01
ID: 39654458
Got it!  Worked perfectly!  Thank you for the coaching.

B.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now