Excel VBA and Finance World

Trying to put in all the Tips related to Excel, VBA and Finance that I think can be useful for those who are in this field

How to create Dynamic chart in excel

Posted by Dibyendu.Sharma
Monday, 12 October 2009
comments (0)


We often come across situations where we need to create a line/column chart to see the performance of sales/share price etc. However expanding the chart series every time there is an update/new data to be added is always time consuming. Therefore if in some way we can make the series of chart data dynamic then it will also make the chart dynamic.
Let see an example: Suppose we have to create a line chart for monthly price for 3 stocks for last 1 year and every month there will be a new addition for the next 1 year. We can easily create it by selecting line chart from excel and having months on column A and share price of stock on columns B,C & D respectively. However if we select a long range to fit the data then wherever there is no data the chart will show blank/space left in the chart which is not a good way to construct, and if we don’t select a long range we have to manually add it every time.
Now let see how we can make the chart dynamic in such case.
Step 1: Create a data table as shown and save file as chat.xlsx




Step 2: Create a name range using Name Manager in Excel 2007 as shown below
MonthValues =OFFSET(StockAvalues,0,-1)
StockAvalues =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)
StockBValues =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)
StockCvalues =OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!$D:$D)-1,1)
Step 3: Now you can create a chart using the Chart wizard but make to have the series data in the following way:

=SERIES(Sheet1!$B$1,chart.xlsx!MonthValues,chart.xlsx!StockAvalues,1)
=SERIES(Sheet1!$C$1,chart.xlsx!MonthValues,chart.xlsx!StockBValues,2)
=SERIES(Sheet1!$D$1,chart.xlsx!MonthValues,chart.xlsx!StockCvalues,3)



And there you go…..Download the file from here:
http://www.esnips.com/doc/92319a9d-e312-4752-b6e0-bca9bb9cb056/Dynamic-Chart-in-Excel

Labels:

ISERROR Macro in Excel

Posted by Dibyendu.Sharma
Sunday, 11 October 2009
comments (0)

Hi All, I came across a wonderful macro where you can change the formula of a give cell or selection if it gives you error values like #DIV!, #Value! etc. This is a very effective macro for files or excel models where you have to deal with lot of formulas and calculation that affects your output. Below are the steps for the file:


Save this file on your desktop and then open it


Open the model where you want to use the ISERROR formula


Highlight the range you want to use the formula


Hit Ctrl+e to run the macro for ISERROR formula


If you want to undo please use Ctrl+z


Please note this will work for only formulas where cell reference is done and not for the below:


#DIV/0! Text
50 Not a cell reference formula
#VALUE! comments

Below is the macro code attached in the file:
Type SaveRange
Value As Variant
Address As String
End Type


Public OldWorkbook As Workbook
Public OldSheet As Worksheet
Public OldSelection() As SaveRange




Sub If_Is_Error()
'
' If_Is_Error Macro
' Changes =x to =if(iserror(x),"",x)
'
' Keyboard Shortcut: Ctrl+e
'
Dim ManualCalc As Boolean


If Application.Calculation = xlCalculationManual Then ManualCalc = True
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


Dim x As String


' Save current values for later undoing
ReDim OldSelection(Selection.Count)
Set OldWorkbook = ActiveWorkbook
Set OldSheet = ActiveSheet
i = 0
For Each cell In Selection
i = i + 1
OldSelection(i).Address = cell.Address
OldSelection(i).Value = cell.Formula
Next cell


' make each cell is_error="" if it is a formula
For i = 1 To UBound(OldSelection)
If Len(Range(OldSelection(i).Address).Formula) = 0 Then GoTo skip
If Left(Range(OldSelection(i).Address).Formula, 1) <> "=" Then GoTo skip
x = Right(Range(OldSelection(i).Address).Formula, Len(Range(OldSelection(i).Address).Formula) - 1)
x = "=IF(ISERROR(" & x & "),0," & x & ")"
Range(OldSelection(i).Address).Formula = x
x = 0
skip:
Next i


'Return claculation to automatic if required
If ManualCalc = True Then Application.Calculation = xlCalculationManual Else Application.Calculation = xlCalculationAutomatic
' If ManualCalc = True Then MsgBox "Calculation left on manual." & Chr(10) & "Press F9 to calculate sheet"


Application.ScreenUpdating = True


' Specify Undo
Application.OnUndo "Undo the IsError macro", "UndoIsError"


ManualCalc = 0


End Sub


Sub UndoIsError()


On Error GoTo Problem


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = True


' Make sure the correct workbook and sheet are active
OldWorkbook.Activate
OldSheet.Activate


' Restore saved formulae
For i = 1 To UBound(OldSelection)
Range(OldSelection(i).Address).Formula = OldSelection(i).Value
Next i
Exit Sub


Problem:
MsgBox "Can't undo"
End Sub


Link for download:
http://www.esnips.com/doc/01ebaaa2-2b7d-4300-9fdc-ad01f06bb864/ISERROR-macro

Labels: ,

How to use CHOOSE function in Excel

Posted by Dibyendu.Sharma
Saturday, 10 October 2009
comments (0)


We often come across situation where we have to select from a list of available options in excel. Let’s take an example different 5 regions for sales and for each region you have assigned numerical values from 1-5. Now you wish to just put any number 1-5 to get the sales for that region. And in this case your CHOOSE() function comes handy.
Lets first know how a choose function works:
The syntax of CHOOSE() function is CHOOSE(index_value, value1, value2….value5)
Here index_value is the position of the number in the list of values to return. In this case it is 1-5. A value can be any one of the following: a number, a cell reference, a defined name, a formula/function, or a text value. Therefore if you use a CHOOSE() function like:
CHOOSE(3,”Tea”,”Coffee”,”Hot Chocolate”). Then this would return Hot Chocolate.
Now back to our situation described above.
Step:1  Assign number from 1-5 to each region like
1 Asia Pacific
2 UK
3 US
4 Africa
5 Middle East
Step 2: You can now input any number from 1-5 in Cell D3 and use the 2 CHOOSE functions in any other cells as shown here CHOOSE($D$2,B3,B4,B5,B6,B7) and CHOOSE($D$2,C3,C4,C5,C6,C7) to get the desired result. Use the following attached sample file for your understanding.
 
http://www.esnips.com/doc/2c920ebf-1913-401b-b348-18d778142c88/Excel-CHOOSE%28%29-Function




How to avoid selection of range in excel that are grouped/hidden and only select the visible ranges

Posted by Dibyendu.Sharma
Friday, 9 October 2009
comments (0)

How to avoid selection of range in excel that are grouped/hidden and only select the visible ranges.

We often come in situation where we have a range of data say quarterly reviews with annual reviews of sales. In such cases mostly the quarterly data is grouped/hidden to show that they are easily recognized separately from the annuals figures. However they is a problem when we want to copy and paste the annual data for some other calculation. In this case if we copy paste we would see the whole of quarterly and annual data selected together instead of only annual data. Therefore to avoid this we have a solution.

Step 1. Group all the quarterly data as shown below. Use ALT+D+G+G to group for a selected rage and then click on the + sign on the top of the sheet to hide the quarterly data.

Add Image

Step 2: Select the data you want to copy for annuals, in this case let’s say all of the data. After selection use the ALT+ Semicolon (;) on the key board. This will only highlight the selected ranges which are visible. Alternatively you can use Function Key F5, this will popup the Go To window. Then click on “Special” on the Go To window and you will see a list of radio button to select from. Use the “Visible cells only” and click OK.

Step 3: Now once you have the highlighted range you can copy paste which will only paste the annuals value.

Labels:

Subscribe to: Posts (Atom)