A simple solution for Investment Portfolio Management



Here is a simple solution developed by me for Investment Portfolio Management which requested by my cousin. He is having a novel technique of tracing his investments. Since I did not get his permission, I did not explained his technique. But I implemented a generic method using Weighted Average. This solution helps to add market rate whenever you have data with you. If it is feasible, this can be modified to fetch data from website and add to your database. Most of the user may prefer to enter their own database.

The applications has four sheets

• Status
• MyInvestments
• RateData
• FetchWebData

In the MyInvestments Sheet you have enter the asset details, and periodical market rate in the RateData sheet

When you enter the date in the Status sheet, it will show the value of the investment.

Once again I reiterate that I will send this excel file free of charges, if you send me an email.
My email id is karthiganesh@yahoo.com

Cheers!

Frequently used Functions in my solutions!

Here is the secret ingredients of my EXCEL-VBA recipes.

I will use the following functions extensively :

1. INDEX
2. MATCH
3. VLOOKUP
4. HLOOKUP
5. SUMIF
6. SUMPRODUCT

Apart from this I have my own collection of UDFs. Here I have listed their functionality only. Please dont try to criticise these methods. I am very clear that these are my own collections. They may be "better than the best" or "not bad" or "more worst than the most worst". In any case point to me as it will help me to improve my skills. I am still learning EXCEL-VBA.

1. Procedure to update a single cell named range
2. Function to retrieve the value in single cell named range
3. A generic Function to find the last column in a table or column number of a particular value
4. A generic Function to find the last row in a table or row number of a particular value
5. A generic function to compose SQL statements from a range of cells
6. A Procedure to Compare two excel worksheets
7. A function to compose EXCEL CELL formula from a user design
and many more...

I may update this post very frequently.

If you want know more about a particular Function or UDF, feel free to write to me karthiganesh@yahoo.com

Simple EXCEL-VBA Report Generation Guidelines

What are the basic components of an Excel Report Generator?
I like modularisation and customisation while coding. EXCEL-VBA offers entirely different kind of customisation compared to other programming language. In the Project Explorer you can see

Microsoft Excel Objects, Forms, Modules categories.

• You can place your procdure/function in any of the objects. I place modules like this:
  Event Handlers are placed inside ThisWorkBook (usually Cell Double Click, Right Click, WorkBookOpen events and handles by these event handlers)
• Procedures and Functions specific to a particular Sheet are placed inside the Sheets
• Utility procedures/functions used by all objects are placed here.

A typical procedure skeleton is like this

Public Sub GenerateReport
...{declaration of variables}
...{query building}
...{fetching data and populating into sheet}
...{formatting data as per their content (number, date etc)}
...{performing sort, subtotal, filter etc}
End Sub

Then call this procedure from the click event of a command button placed in the sheet.

Prominent advantage of EXCEL-VBA lies in customisation of Queries. You can keep your queries in a worksheet cell. Criteria can be easily composed into a cell using cell formulae. For example consider the following example

SELECT EMPNO, NAME, DEPTNO FROM EMP
WHERE DEPTNO=20

You can type this SQL statement in two cells and design the excel sheet as shown below

In the above sheet, the cell contents are like this
A5 is a single point cell for complete SQL SELECT statment. It is concatenation of below cells
=A6&A7

A6 is just SELECT statement entered as text
SELECT EMPNO, ENAME, DEPTNO FROM EMP

A7 is a formula to compose the criteria for DEPTNO it is entered as a formula
=" WHERE DEPTNO="&C3

Note that the user will enter the required DEPTNO in cell C3.
Now you may think that this will reveal the SQL SELECT statement to the users. If you are concerned about the security of source code, you can type them in a separate sheet and protect it using appropriate security measures. It is available in EXCEL. Those security guidelines will be posted in subsequent posts.

Keep on visiting my blog for better understanding EXCEL-VBA from an expert point of view. I assure you my prompt reply and support email me karthiganesh@yahoo.com

EXCEL-VBA for Reporting

MS-EXCEL as Reporting Tool for ODBC Database

Almost all RDBMS Databases are coming with sophisticated Front End tools for developing Data Entry forms, Reports and Charts. Then what is the need for using MS-EXCEL for the same purpose. I think these may be the reasons...

• MS-EXCEL is most easy to use spreadsheet application. Business users know MS-EXCEL very well.
• The existing knowledge of MS-EXCEL can be leveraged for better presentation. I mean the user can customize the reports. The developer can concentrate on implementing complex business logic rather than implementing User Interface. Most of the user will use that data effectively if they are able to customize the presentation (Like self-cooked meals).
• Business experts with MS-EXCEL knowledge are able to process the data on their own. There is no need for explaining the business logic to the developer/analyst. Or atleast it will help them to explain the logic better. (Earlier in my career, when our Factory Manager explains me his requirement, I used to implement the same in QuatroPro and get his approval. This helped me to develop robust FOXPRO applications)
• It is cost effective solution to use MS-EXCEL as a Front End Tool
• It can be used for WHAT-IF analysis. The same data can be represented for different scenarios by changing some parameters. MS-EXCEL ability for number-crunching can be better utilised for this purpose. (Practically this has numerous application. One of my spreadsheet application for Producation Planning is designed as WHAT-IF analysis tool)
• There are many... But I want to cut is short as you may feel it is boring.

One of my earlier post in this blog demonstrated how to fetch and populate EMP table into MS-EXCEL. This post can be considered as continuation of that post.

My Tool for comparing two EXCEL workbooks using EXCEL-VBA

How to compare two Excel WorkBooks?

There are many tools and solutions for comparing two workbooks being used. Many MVP for EXCEL suggested a method for this. I want to share the method which I am currently using for comparing two books. For this purpose I created a small and simple workbook.

Here is the screen shot of the workbook. Bigger view of the same screen shot

You can email me to have your own copy of this tool free of cost.

I dont know how to publish this XLS file for sharing. I need help in this regard.

One more quick method for single sheet workbook is

• Open two books in different windows
• Create a new blank workbook
• Type the formula =[Book2]Sheet1!$A$1=[Book1]Sheet1!$A$1
• Change the above formula from absolute address to relative address (remove $)
• =[Book2]Sheet1!A1=[Book1]Sheet1!A1
• Copy this formula to required range area
• You can see TRUE or FALSE based on the values in both sheets
• You can use FORMAT / CONDITIONAL FORMATTING
• set Cell Value Equal to FALSE
• select FORMAT
• select PATTERNS and select a flourescent color.
  
  I hope you will like and use this trick for comparing two sheets.
  
  Cheers friends! Have a look at my other posts also. karthiganesh@yahoo.com

Other Web Resources for EXCEL-VBA Programming

Other Web Resources for EXCEL - VBA Programming.

When I started developing macros for the first time using LOTUS 123 during 1992-93, I could not get help from any professional. I had to rely with 123.hlp file, which was not available with all LOTUS 123 installation. In our Computer Centre only one PC has HARD DISK, all other PCs are booted from floppy only!!! Internet is available in big cities only. So by sitting late night, I learned LOTUS 123 macro and played around. Mostly to amaze my friends and colleagues.

First macro was put into use in the Ageing Report developed by me in QuatroPro Spreadsheet. That time also I relied with QuatroPro help file. During 1995 situation is somewhat better as most of the PCs had Hard disk.

During Late 90's When I told my colleagues that ACCESS table can be better presented in EXCEL, they believed me. But they thought that I am talking about EXPORT command in ACCESS. When I reiterated them it can be directly fetched from a command button in EXCEL and summarize the sales data. They thought that I was crazy!!! But When I showed my first application they became crazy!!!

Sorry for the long history. But It might give you some idea that information is power in these IT Age. So here is a partial list of popular websites which will give you better tips, tricks and techniques (than me!!!) for EXCEL-VBA .

http://www.excel-vba.com/

This is one of the most visited website by EXCEL-VBA programmers. It has very good introduction. You may know VBA means Visual Basic for Application. But VBA-EXCEL, VBA-ACCESS, VBA-WORD, VBA-OUTLOOK are explained in this website very simply. You please visit this website. If you prefer you can purchase the lessons.


http://www.anthony-vba.kefra.com/

This page is popularly known as Anthony's Excel VBA (Macro) Tutorial. It has many scientific, mathematical and business algorithms implemented in EXCEL-VBA functions, procedues and workbooks. Good starting point for any aspiring EXCEL-VBA Developer. The basic tutorial section is one the excellent work.

http://xl.barasch.com/

I like this website very much. It has many examples, which are relevant to most of the situation. Whenever I require a routine, first I will try in this website. It has many useful and strange functions of great useful. Must visit website for all EXCEL-VBA developers.

http://www.mindspring.com/~tflynn/excelvba.html

It is a great website for collection of useful utilities in EXCEL-VBA. Frequenty I refer this website and learn a technique and apply for my solution. It has good link to other excel resources.

http://www.ozgrid.com/
One of the useful and free webresource for VBA programmers. It has sample VBA code, which can be effectively customized for our requirements. The combo box for FAQ is having a list of useful FAQ which makes navigation very simple.

http://www.angelfire.com/biz7/julian_s/julian/julians_macros.htm

This has many simple yet useful tricks and techniques

http://www.functionx.com/vbaexcel/

This website I visited recently and found it is organised for self learning and the content is really valuable and lucid. The lesson summary will give us option to start reading relevant content.

Last but not the least

http://kgexcel.blogspot.com/

It is designed my me!!!

and there is official website from Microsoft, which is the only comprehensive help resource for EXCEL-VBA programming.

Cheers! Enjoy EXCEL-VBA Programming

Here is my assurance of prompt reply to your queries. My email id karthiganesh@yahoo.com.

What is a PivotTable?

A PivotTable is a summary table of voluminous data. It used to summarize the data based on the selected data values. Consider
the following example

SalesPerson	Month	Volume
CHAN	OCT-07	450
LEE	NOV-07	430
MANI	NOV-07	230
CHAN	NOV-07	110
MANI	DEC-07	420
LEE	DEC-07	110
CHAN	DEC-07	210

From the above table,

• If we need the total sales volume of each SalesPerson, then we can use DATA / SUBTOTAL command for each change in
  SalesPerson
• If we need the total sales volume of each Month, then we can use DATA / SUBTOTAL command for each change in Month
• If we need SalesPerson and Month wise breakup in a matrix format, we cannot use DATA / SUBTOTAL command

PivotTable can be used for the above summary report. A sample PivotTable will look like this.

	OCT-07	NOV-07	DEC-07	Total
CHAN	450	110	210	770
LEE		430	110	540
MANI	230	420	650
Total	450	770	740	1920

Procedure

1. Prepare the List (You can type or fetch the data from any ODBC compatible database. Refer my earlier post in this blog)
2. Select the List
3. Select Data / PivotTable and PivotChart Report menu command
4. Select the output location (it can be a new sheet or part of the existing sheet)
5. Select the LAYOUT button and
6. Place the SalesPerson in Row area
7. Place the Month in Column area
8. Place the Volume in the Data area
9. Then Finish, now you can see the PivotTable

In the same way you can generate PivotChart

Sample Scenarios

1. A Warehouse wise, product summary
2. Yearwise, Monthwise, Product Sales/Purchase
3. Department wise, Year wise admission for a college

Other Points

• You can have 3 dimensional PivotTables by using Page Field

Once Again I am here to help in EXCEL based solutions, as a service to society. Your comments are driving force behind the success of this blog.

How can I learn EXCEL Programming easily, quickly and effectively?

You can learn EXCEL Programming very easily!
Believe me it is a child play!!
Yet the result will be an Expert Solution!!!

If you know EXCEL operations very well, you can code for the same. For example, you may wish to subtotal the EMP records based on DEPT. (Refer my previous Blog Post for programmatically fetching data from ORACLE to EXCEL Worksheet).

You know using DATA / SUBTOTAL menu. If you want to do the same from a EXCEL VBA macro, then You need not learn EXCEL VBA language. Here is the shortcut.

Procedure
----------

• Start recording macro. (TOOLS / MACRO / RECORD NEW MACRO)
• Now you can start working as usual. In this case you find the subtotal of the EMP records based on DEPT.
• Stop recording macro (TOOLS / MACROS / STOP RECORDING)
• Now Start VBE (Visual Basic Editor) (TOOLS / MACROS / Visual Basic Editor)
• In the Project Explorer, Expand the Modules. You can see 'Module1'. Open the 'Module1' by double clicking it.
• VOILA!!! You can see the code for finding subtotal.
• COPY, PASTE and CUSTOMIZE

If you ask me what is EXCEL-VBA programming!!!

for a NOVICE Programmers

RECORD, COPY, PASTE and CUSTOMIZE

For Advanced Programmers

Will tell in the subsequent posts. Keep visiting my Blog.

How to display the EMP records from ORACLE?

A 100 million question among IT managers???

MS-EXCEL is one of the most useful business tool, but can I display the RECORDS from our company Database... say from ORACLE?
This will enable the executive to process the data as per their working style and make useful decision!!! This facility offers flexibility and fidelity.

Here is a simple solution from kg.

MS-EXCEL can access data from any ODBC compatible RDBMS. Being a popular RDBMS of many corporate choice, I am going to demonstrate how to access ORACLE data using MS-EXCEL.

Step by Step Procedure

• Create a Data Source Name in ODBC. Using
  Control panel
  Administrative Tools
  Data Sources (ODBC)
  Add an User DSN (You can get help from competitive person from your organization)
• Start MS-EXCEL
• Start Visual Basic Editor (Tools / Macro / Visual Basic Editor)
• In the VBE (Visual Basic Editor), view the Project Explorer
  (VIEW / Project Explorer) I use CTRL+R key.
• Double click on the ThisWorkBook. You can see the CODE WINDOW. In this select WorkBook in the Object List and select Open in the Event List.
  
• Type the following code in Workbook_Open procedure
  
  Private Sub Workbook_Open()
  Dim cnEmp As ADODB.Connection
  Dim rsEmp As ADODB.Recordset
  
  If cnEmp Is Nothing Then
  Set cnEmp = New ADODB.Connection
  With cnEmp
  .ConnectionString = "DRIVER={Microsoft ODBC for Oracle};UID=SCOTT;PWD=TIGER;"
  .Open
  End With
  End If
  
  If rsEmp Is Nothing Then
  Set rsEmp = New ADODB.Recordset
  rsEmp.Open "select * from emp", cnEmp, adOpenStatic, adLockBatchOptimistic
  End If
  
  'To display Column names
  For i = 1 To rsEmp.Fields.Count
  Sheets("Sheet1").Cells(3, i).Value = rsEmp.Fields(i - 1)
  End If
  Sheets("sheet1").Range("a4").CopyFromRecordset rsEmp
  End Sub
  
  Here I uploaded the excel workbook also.

I hope that this is a simple yet more useful solution. Once you tasted, I am sure you will not leave this elegant tool. It can be put into effective use by ORACLE DBAs.

Once again, You can email me your queries. I am ready to give my assistance.

What is Excel Programming?

MS-EXCEL can be programmed to meet our custom requirement effectively. An EXCEL prgrammer write code in VBA (Visual Basic for Applications). Hence it is also called as EXCEL-VBA Programming.

So any existing VB Programmer can do EXCEL prgramming.

Here is a simple solution to add two numbers. It is just to show how to write VBA Functions. So dont argue with me what is the use of this solution. I know that the simple and well known cell formula =A1+B1 will do this.

• Start Visual Basic Editor using TOOLS / MACRO / Visual Basic Editor. (I use ALT+F11 key combination!!!)
• Insert a module (Insert / Module)
• Type the following in the module

Public Function add(ByVal a As Integer, ByVal b As Integer) As Long

add = a + b

End Function

• Now in the worksheet type the following table

TRAVEL FOOD TOTAL

JAN-08 230 430 =add(a1,b1)

FEB-08 210 545 copy the above formula

• In the total column enter the following formula =add(a2,b2) and drag below.

I hope that this will give you a fundamental idea for novice user. For advanced use, they can write many sophisticated function in this way.

Feel free to contact me for any assistance (ofcourse I will assist you free of cost only). karthiganesh@yahoo.com

What can you do using MS-EXCEL?

Literally You can use MS-EXCEL for anything...

* Worksheet
* Word processing
* Presentation
* Front End Tool for popular RDBMS
....

and You too can add one more item to this list...

Here is the list for Advanced / Specialised applications

* Business Intelligence Tool
* What-If Analysis Tool
* Real Time Calculations for Production Specialists
* Statistical Analysis Tool
* Charting Tool

and more.