Solved

Excel Currency Conversion Update

Posted on 2013-11-13
19
309 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 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 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 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

803 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