Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Currency Conversion Update

Posted on 2013-11-13
19
Medium Priority
?
341 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
19 Comments
 
LVL 27

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 27

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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

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

B.
0
 
LVL 27

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 27

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
 

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 27

Accepted Solution

by:
MacroShadow earned 2000 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 27

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 27

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 27

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

715 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