Pro-business Excel VBA Programming

Scenario:

You have a spreadsheet that has amount in US Dollar and you need to convert it to other currency. The conversion should be done automatically whenever the workbook is open. The currency rate should be based on the latest conversion rate. As an example, let's say the conversion would be from US Dollar to Singaporean Dollar. How can we do this?


Solution:

In this situation, we are going to use a web query to capture the current currency rate in the Internet and the "open event" whenever the spreadsheet is open. In this case, we will use the Yahoo Finance Currency Conversion Calculator to provide us the most updated rate that we need.


Here are the step-by-step instructions on how to go about it:

1.) Create a new workbook and rename the first worksheet as "Amount". Rename the second as "Currency Rate".

2.) Open your browser and go to Yahoo Finance Currency Conversion Calculator by clicking this: http://finance.yahoo.com/currency

3.) In the "Currency Converter", Put 1 in the TextBox, then choose "US Dollar" in the first dropdown list and Singapore Dollar in the second one as shown in the figure below:



4.) Click the "Convert" button. Yahoo will display the currency conversion rate of the current date as shown below:



5.) Copy the URL: http://finance.yahoo.com/currency/convert?amt=1&from=USD&to=SGD&submit=Convert

6.) Going back to Excel, click the "Currency Rate" worksheet tab and place Cell A1 as the active cell. In the menu, click Data>Import External Data>New Web Query. The Excel Web Query will appear and in the "Address" field, put (paste) the URL that you just copied and click the "Go" button. See the screen shot below to know how it looks:



7.) Scroll down the window at the portion where you can see the actual conversion rate of USD to SGD. At the left side of it, you will see a small yellow square with an arrow inside pointing to the right. Click on that yellow square and it will change to green color and the arrow will be changed to a check mark as shown in the figure below:



8.) Click the "Import" button. The "Import Data" window will be displayed as shown below and then click the "Ok" button.


9.) The conversion table is now in Excel. Cell E3 has the rate from USD to SGD. Cell C3 has the date of the rate. Click now the "Amount" tab in Excel and put the following in the indicated cells:

• At Cell A1, write "USD Amount"
• At Cell B1, write "SGD Amount"
• At B2, put the following formula "=A2*'Currency Rate'!E3"
• A2 will have the USD Dollar amount to be converted. Initially, let's put 100 dollar in it.
  

10.) Open up the Visual Basic Editor (VBE) and write the following code in "ThisWorkBook" object:

Private Sub Workbook_Open()
    Sheets("Currency Rate").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    Sheets("Amount").Select
End Sub

That's all it takes to do it. Whenever you open up the Excel file, it will automatically update the currency rate and eventually the Singapore Dollar amount in Cell B2 in worksheet "Amount".

Now that you know this and by using your imagination, you can play around with it and make something useful out of it. You can apply the technique to your financial or business calculation.

Three days ago, I was facing a difficult situation. A client of mine was asking me to make percentage-items of a report to sum up exactly to 100%. To visualize what I’m talking about, look at the example of figures below:

# of items ------------->%
7 -------------------------->58.33%
1 -------------------------->8.33%
3 -------------------------->25%
1 -------------------------->8.33%
-----------------------------------------------------------
12 -------------------------->99.99% <------TOTAL

The example above looks incorrect when you add all the percentage items as they are. The percentage total would be 99.99% and not 100%. It is even more incorrect if all percentage items are rounded off to the nearest one. See below:

# of items ------------->%
7 -------------------------->58%
1 -------------------------->8%
3 -------------------------->25%
1 -------------------------->8%
---------------------------------------------------
12 -------------------------->99% <------TOTAL


The total became 99%. But when you put the same calculation to Microsoft Excel, it would display a different result. Please create a New Worksheet in MS Excel and enter the following:

Cell A1 = 7
Cell A2 = 1
Cell A3 = 3
Cell A4 =1
Cell A5 =SUM(A1:A4)

Cell B1 = A1/$A$5

Copy formula of B1 to B3-B4 and copy A5 to B5. Format the entire column B to “Percentage” with zero decimal places. When done, you would see the following result:

# of items ------------->%
7 -------------------------->58%
1 -------------------------->8%
3 -------------------------->25%
1 -------------------------->8%
----------------------------------------------------
12 -------------------------->100% <------TOTAL

The result as you have seen is very interesting. Microsoft treated the value of each individual percentage-item as the number of its formula result and not as what is displayed. This is why the total percentage is 100% and not 99%. In general practices, the MS Excel computation is acceptable to most people and they consider the difference negligible. But there are few people who disagree with this kind of result and consider it unlikely and incorrect to present this type of calculation. Truly, the situation is really a matter of personal point of view and consideration of the person going on this kind of situation.

To satisfy those few people who prefer to have each percentage item to add up to exactly 100%, a VBA script is can be created to do the trick. However, before I present to you the solution, I would like to reiterate that the idea is a matter of preference and most likely debatable to most people because they prefer the Microsoft Excel way. It is correct to some people and the rest of them think otherwise.

Run the script below:



Sub ComputePercentage()

Dim i As Integer
Dim x As Integer
Dim Tot As Single
i = 1
'ROUTINE TO GET THE ROW LOCATION OF THE PERCENTAGE TOTAL
'AFTER THE LOOP i HAS THE ROW NUMBER
Do While Not Cells(i, 2).Formula Like "=SUM*"

i = i + 1

Loop
'COMPUTE THE PERCENTAGES
Tot = 0
For x = 1 To i - 1

If x <> Cells(x, 2) = Round(Cells(x, 1) / Cells(i, 1), 4)

Tot = Tot + Cells(x, 2)

Else

Cells(x, 2) = 1 - Tot

End If

Next x

End Sub

By the way, before you run the program, format the Column B to Percentage with 2 decimal places. When you run the program above, it would give you the following result.

# of items ------------->%
7 -------------------------->58.33%
1 -------------------------->8.33%
3 -------------------------->25.00%
1 -------------------------->8.34%
---------------------------------------------------------
12 -------------------------->100% <------TOTAL

Notice that the last percent number is slightly incorrect. When you divide 1 with 12 the quotient is 8.333333… I will say it again, to those people who prefer it this way, they might consider the inaccuracy of the last number as so small as to be neglected.

It is for you to decide (or your client) which one to use.

As I was doing some research for what kind of information the internet has about Excel and VBA, I have learned that many websites have already been giving the kind of information that I had in mind when I started this weblog. This is a little bit frustrating, nevertheless challenging to come up with a niche of my own. I have decided to narrow down my objectives to provide specific example of business solutions using Excel and/or Excel VBA. In my future articles, I will be posting basic Excel and VBA functions, and some articles will tackle combinations of these functions to develop or solve a certain business or financial solutions, real examples to solve your number-crunching problems.

Business solutions means vital information is needed to be dug up from a haystack of data residing in your DBMS servers. I know that some of you would say that most of these DBMS, like Oracle, have their own report builder and there’s no need for Excel, thus there’s no need for this weblog. It is correct that reports can be done using the DBMS report builder. But for ad-hoc reports that you need to provide at once, I don’t think that you have the time to ask for your IT guys to create one for you immediately especially when officer approval is needed for your new-report request. Excel comes in handy with this kind of situation. As long as you have the resource to have an extracted data file from the DBMS server or you have an authority to extract them yourself, you can create the report that your boss needs and fast.

Knowing to generate information from a raw data is the skill you need to link business and technology... It's business intelligence in its own way.

To all my readers, this Excel VBA weblog is your BUSINESS TOOL. Enjoy!