Data Source Is Out Of Control

Have you experienced to create an MS Excel reports wherein the source of data is a .CSV file that is extracted from an Oracle system? If yes, then maybe you have also experienced the situation that I have been through.

A few months ago, I closed a contract to develop Purchase Order (PO) performance reports and Sales Order (SO) performance reports with one of my old clients. They looked very simple at first because there was no need for me to create data entry modules for the program. The source of the data would be coming from their Oracle system. Records would be entered from different countries across Asia Pacific and stored them into one central database server. Weekly, they would extract the records from it and convert it to Comma Separated file or .CSV file. My Macro program would import those contents of that .CSV file to my worksheets and somehow needed to consolidate them for reports and analysis purposes.

At first, everything went smoothly. One day, the project liaison called and told me that there have been errors in almost all the reports. She sent me a copy of the macro file and source data file to check on the problems. As I was checking the reports of the Excel file one-by-one, reports were devastated. I investigated for hours all the macro modules but I hadn’t seen any clue of what happened to them. So, I asked myself; “How come a program in a good running condition suddenly gives incorrect figures in almost all reports?” The answer is actually right under my nose. When I checked the records in details, I saw the null values of all the Date Fields like “Actual Shipment Date”, etc. were changed. Originally, if the null date field was converted to .CSV file, the content was “NULL”. It was changed to dash (“-“) and eventually would give incorrect values in reports. To correct the problem, I spent days to modify the program modules and worksheet cell formulas that are related to all the date fields. Whew! It was tedious.

That was a learning experience for me. I didn’t see that coming and hadn’t prepared for it. Partly, it was my fault because I presumed that the records format was final and didn’t realize that the data-entry program and data-conversion program were not mine and it could be changed anytime.

For that particular reason, I requested the project liaison to collaborate with their Oracle programmer to advice us if records structures were altered.

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.

Number Rounding Off Issue

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.

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.

Creating Tables and Columns at Run Time

The following code is an example of creating a Table and Columns using ActiveX Data Objects (ADO):

Sub CreateEmployee()

Dim cn As ADODB.Connection
Dim strSQL As String
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=c:\test.mdb;"
cn.Open
strSQL = "CREATE TABLE `Employee` (`Last Name` varchar(30), `First Name` varchar(30), `Age` integer)"
cn.Execute strSQL
cn.Close

End Sub

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.

Three Main Objects of ADO

Connection Object

This object is the one used to connect to database, which is then used to execute commands against the database or retrieve a Recordset. The object has the ConnectionString property that is used to specify the database you want to connect to. The Open method establishes the database connection. The Close method releases the connection and the memory used by the object.

The Connection String can be generated at run time using the DataLinks. Please see the topic Building Database Connection String Programmatically.


Connection String Attributes:

Provider – The provider name or driver name that ADO will use to access the database.

User ID – When needed, it is used by the provider to establish the proper rights for accessing the database.

Password – To validate the user, the user’s password is needed by the provider.

Integrated Security – Setting this value to SSPI, the driver will use the Windows NT Integrated
Security. User ID and Password are not used when this is done.

Data Source – If you are connecting to an Access database, this should be set to the file and complete path of the file. This should have the machine name when connecting SQL Server.

Initial Catalog – This attribute should have the database name when connecting to SQL Server.

Recordset Object

This object is important because it is used to manipulate databases. It is a memory allocation in a client computer or server that has a set of rows of one table or more of the database. With Recordset, you can add, edit, delete and update records of the actual table in the database.


Command Object

This object has the information about the command to be executed. The command object could have a Query (SQL) command, Table, View or Stored Procedure.

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.

Creating MS Access Database File using DAO

The following code is a procedure to create an Access Database file with the use of DAO component:



Sub CreateDB(vF As String)

Dim wrkDB As Workspace
Dim dbNew As Database
Set wrkDB = CreateWorkspace("",Application.UserName, "")
If Dir(vF) <> "" Then Exit Sub
Set dbNew = wrkDB.CreateDatabase(vF, _
dbLangGeneral, dbEncrypt)
dbNew.Close

End Sub



DAO is an old Windows Component to manipulate database. The code above is one good reason to use DAO even if ADO is already available with Windows. I am not sure if there is a way to create an Access-file using ADO, I have researched the internet and I have tried so many times to do it using ADO but I failed. So I stick with DAO to do the trick. If you know a way to do it with ADO, please let me know. It will help a lot of people out there.

In manipulating the database like creating tables and columns, adding, editing and deleting records, I recommend using the ADO instead of the DAO. It is easier. To know more about ADO, please read the topic: Microsoft ActiveX Data Objects (ADO)

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.

Mutual Fund Calculator

A Mutual Fund is an investment that you put your money in a big pool of money that is invested in different investment instruments in a diversify manner. I am not going to discuss in details about mutual fund. I assume that when you read this article, you are looking for a way to calculate how much you need to invest in a regular basis to attain your financial objectives. But in case you don’t know Mutual Fund, you can google it and you will see a lot of information in the internet.

Situation:
You are working hard and you have a few dollars in excess from your salary. You want to invest this money in a way that you don’t have to check it in a daily basis because you are busy with your job. You want to make sure that this money is invested wisely and you want someone to manage it for you so you can just check on it in a monthly or quarterly basis. Mutual Fund is ideal for this situation. Your objective is to know the amount of money to spare in a monthly basis to obtain your target specific amount of money in a specific time frame. Excel is the tool you need to forecast your Mutual Fund. This tool is useful when you want to buy a house and lot, or a car in the future.

Putting money in Mutual Fund in a regular basis is called Dollar-Cost Averaging. It is a smart investment strategy. This method works in your favor no matter what happens to the market. I will show you a way to forecast Mutual Fund Dollar-Cost Averaging, in case you want to do it.

Solution:
Copy the following screen grab in your Excel worksheet. Cells B3, B4 and B5 are the data entry fields for annual interest rate, number of investment years and the target amount respectively.















Cell B8 has the formula to compute for the amount to put monthly in Mutual Fund. The formula used the PMT function. The formula is as follow:

=-PMT(B3/12, B4*12, 0, B5)

First parameter, Interest Rate from Cell B3, is divided by the number of months per year, 12 months.

Second parameter, Number of Years, from Cell B4, is multiplied by 12 to determine the total number of months.

Third parameter is zero. The parameter is used for other function of PMT.
Fourth parameter has the target amount.

(Please see PMT Function)

Maybe you are thinking that interest rate is not easy to come up with unlike the number of years and target amount. To bring about the correct estimate of interest rate, you need to choose the Mutual Fund that you want to put your money with. Do a research about its performance from the business newspapers and financial websites. Let’s say, you are planning to invest for the next 5 years and your target amount is $500K. Look for the result percentage of the 5-year performance of the Mutual Fund and use that as the basis for your interest rate. Please note that past performance is not a guarantee of future results, however it is a good estimate. If, for example, the interest rate you’ve got is 8.5%, let’s enter the 3 parameters to B3, B4 and B5. The result is you have to spare $6,716.60 per month for 5 years, as shown below:

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.

A Niche Of My Own

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.

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

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.

PMT Function

This function returns a Double Data-Type specifying the payment for an annuity based on periodic, fixed payments and a fixed interest rate. Syntax is as follows:


PMT(rate, nper, pv, fv, type) As Double


Parameters

Rate - A required parameter specifying interest rate per period.

NPer - A required parameter specifying total number of payment periods in the annuity.

PV – A required parameter specifying present value that a series of payments to be paid in the future is worth now.

FV – An optional parameter specifying future value or cash balance you want after you've made the final payment.

Due – An optional parameter. Type of payment due date that specifies when payments are due. Valied values are: 0 or omitted means payments are due at the end of the period, 1 means payments are due at the beginning of the period

For examples, please see: Mutual Fund Calculator

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.

Project Explorer

In the Visual Basic Editor (VBE), the Project Explorer is located at the upper-left corner of the client area. Below shows a screen grab of this window:

The Project Explorer is a tree structure that has top nodes that represents the currently open Excel VBA Projects. Every Excel Workbook has a corresponding project node that can be explored by clicking its sub-nodes. Each project node has 4 sub-nodes; Objects, Forms, Modules and Class Modules.

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.

Properties Window

The Properties Window displays the properties of an object, in which properties can be managed and modified. Below is the close-up display of the Properties Window:

At Design Time, the Properties Window can be used to change some of the properties of the object. But, some properties are read-only and cannot be altered. Some properties can only be changed at design time and some can only be changed at run time.

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.

F1, The Help Key

Excel has two sets of help group files. When you press the F1 key while you are at the Excel workbook, you will get the “Excel Help” that has the help file for Excel’s workbook functions, formulas, navigation, etc.

At the VBA Editor, when you press F1, you will have the “Excel VBA Help”. This help file is very useful when you are programming Excel Macros. It has programming samples and explanation on how to accomplish a certain coding task. I often use this Excel built-in help files and it saves me most of the time.

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.

Microsoft ActiveX Data Objects (ADO)

Microsoft ActiveX Data Object (ADO) is a Windows 2000 (or later) component for accessing databases. It doesn’t need to be distributed because it belongs to its core services.
ADO has the object name “Microsoft ActiveX Data Objects 2.x Library”. The “x” refers to the version number and it varies on every machine. Loading this, please see Referencing Object discussion.

The first step to making use of the ADO is connecting to the database. You need to have the correct “Connection String” prior to connecting to the database. If you don’t know exactly what Connection String you have to use, you can use the Connection String builder I discussed in Building Database Connection String Programmatically. Once you have your Connection String, you can store it in a variable or you can save it in worksheet cell for future use with your program.

Below is a sample code on how to make connection to the database:

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\test.mdb;"

If your Connection String is in a cell in a worksheet, for example, it is in Cell A1 of Sheet1, the Connection String in the code is can be replaced with the following:

cn.Open Sheets(“Sheet1”).Cell(1,1)

or,

cn.Open Sheets(“Sheet1”).Range(“A1”)

Once connected to the database, you can now do the data manipulation. You can create your own table and columns. You can add, edit or delete a record to the database.

In my future posting, I will include the following topics to help you in learning ADO in details:
3 Main Objects of ADO

• What is a Recordset?
• What is a Cursor?
• Executing a Command with ADO
• Retrieving and Manipulating Data with ADO
• ADO – Open Method
• ADO – Close Method
• Updating Database using ADO

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.

Creating MS Access Database File using DAO

The following code is a procedure to create an Access Database file with the use of DAO component:

Sub CreateDB(vF As String)
Dim wrkDB As Workspace
Dim dbNew As Database
Set wrkDB = CreateWorkspace("",Application.UserName, "")
If Dir(vF) <> "" Then Exit Sub
Set dbNew = wrkDB.CreateDatabase(vF, _
dbLangGeneral, dbEncrypt)
dbNew.Close
End Sub

DAO is an old Windows Component to manipulate database. The code above is one good reason to use DAO even if ADO is already available with Windows. I am not sure if there is a way to create an Access-file using ADO, I have researched the internet and I have tried so many times to do it using ADO but I failed. So I stick with DAO to do the trick. If you know a way to do it with ADO, please let me know. It will help a lot of people out there.

In manipulating the database like creating tables and columns, adding, editing and deleting records, I recommend using the ADO instead of the DAO. It is easier. To know more about ADO, please read the topic: Microsoft ActiveX Data Objects (ADO)

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.