Sensex vs Nifty

Ever wondered why Sensex is around 25,000, whereas Nifty is only around 8,000. 

The table below provides a comparison between the two. See if you can spot the answer.

                

Features	Sensex	Nifty
Base Year	1978-79	1994-1995
Base Value	100	1000
Constituents	30	50
Calculation Method	Free-Float Capitalization (01-Sep-2003)	Free-Float Capitalization (26-June-2009)
Location	Mumbai	Delhi
Authority	Bombay Stock Exchange (BSE)	National Stock Exchange (NSE)
Base Capital	N/A	2.06 Trillion Rs.
Start Date	01-01-1986	03-11-1995

The trick is in the base year, as more time elapsed for Sensex, the index has multiplied more times even though the base is smaller. 

Excel copy from closed file with different column mapping

Private Sub CommandButton1_Click()

Dim wb1 As Workbook
Dim ws1 As Worksheet

Dim wbCopy As Worksheet

Set wbCopy = ActiveSheet

Pathname = ActiveWorkbook.Path & "\Files\"
Filename = Dir(Pathname & "*.xlsm")
Set wb1 = Workbooks.Open(Pathname & Filename)


With wb1.Sheets(1)
 .Range("B4:H4").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
End With

wbCopy.Activate
    Range("B4").Select
    ActiveSheet.Paste
    Range("I4").Select
wb1.Activate
With wb1.Sheets(1)
    .Range("K4").Select
    .Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
End With
wbCopy.Activate
    Range("I4").Select
    ActiveSheet.Paste
wb1.Close



End Sub

VBA Excel - Run macro for copying formula in each file of a folder

Private Sub CommandButton1_Click()
Dim Filename, Pathname As String
Dim wb As Workbook

    Pathname = ActiveWorkbook.Path & "\Files\"
    Filename = Dir(Pathname & "*.xlsm")
    Do While Filename <> ""
        Set wb = Workbooks.Open(Pathname & Filename)
        DoWork wb
        wb.Close SaveChanges:=True
        Filename = Dir()
    Loop
End Sub

    Sub DoWork(wb As Workbook)
    With wb
    Dim rng
rng = "P4:P23"
.Sheets(1).Range("P4").Select

Selection.AutoFill Destination:=.Sheets(1).Range(rng)
    .Sheets(1).Range(rng).Select
       
    End With
End Sub

Excel- find file name only from a directory path

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",LEN(A1))),LEN(A1)))

Source: http://stackoverflow.com/questions/18617349/excel-last-character-string-match-in-a-string

VBA - sheet creation and row copy from a consolidated sheet

Module 1 : creation of list of unique values

Range("A1:A10").Select
    Selection.Copy
    Range("G1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveSheet.Range("$G$1:$G$10").RemoveDuplicates Columns:=1, Header:=xlYes

Module 2: Creation of sheets from list.

Taken from : http://ccm.net/faq/27361-excel-a-macro-to-create-and-name-worksheets-based-on-a-list

Sub CreateSheetsFromAList() 
    Dim MyCell As Range, MyRange As Range 
     
    Set MyRange = Sheets("Summary").Range("A10") 
    Set MyRange = Range(MyRange, MyRange.End(xlDown)) 

    For Each MyCell In MyRange 
        Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet 
        Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet 
    Next MyCell 
End Sub 

Module 3: Copy based on list

Algo: run loop for unique values

Find each row and copy to respective sheet.

VBA example - Multiple condition and nested loop

Private Sub CommandButton1_Click()
Dim x As Range
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ActiveWorkbook
Set ws = wb.Sheets("July")

Dim LastLine As Integer

Dim rng As Range

Set rng = ws.Range("A2:D28")

Dim i As Integer
Dim j As Integer
Dim s As String

Dim Analyst As String
Dim Prime As Integer
Dim SubPrime As Integer
Dim AltA As Integer

For j = 2 To 10
Let s = "A" & j
Let Analyst = Range(s).Value
Let Prime = 0
Let SubPrime = 0
Let AltA = 0
    For i = 1 To 27
        If rng(i, 4) = Analyst And rng(i, 3) = "Prime" Then Prime = Prime + 1
        If rng(i, 4) = Analyst And rng(i, 3) = "SubPrime" Then SubPrime = SubPrime + 1
        If rng(i, 4) = Analyst And rng(i, 3) = "Alt-A" Then AltA = AltA + 1
   
    Next i
    Let s = "B" & j
    Range(s).Value = Prime
    Let s = "C" & j
    Range(s).Value = SubPrime
    Let s = "D" & j
    Range(s).Value = AltA
Next j

End Sub

List of editors of English Newspapers in India

The recent announcement that Dr. Malini Parthasarathy will become first woman Editor of The Hindu on February 1, 2015 set me thinking about the gender distribution for "The Editor" designation. So, I tried compiling a list for some English newspapers. It is astonishing that most of the websites do not mention their editors in "About us" page. 

I found only 2 women editors (highlighted in pink) in the 8 newspapers which I checked. Now the sample is too small but still shows how skewed the ratio is. So here is my compilation.

Newspaper	Editor	Source
The Hindu	Dr. Malini Parthasarathy	Wiki
Business Line	Mukund Padmanabhan	Wiki
The Times of India	Jaideep Bose	Times Group
The Economic Times	Rahul Joshi	Times Group
Hindustan Times	Sanjoy Narayan	HT Media
Business Standard	A. K. Bhattacharya	Business Standard
Deccan Chronicle	A. T. Jayanti	Wiki
Financial Express	M.K. Venu	Wiki

P.S. This list can come handy during interview time because the interviewer may trick you into telling which newspaper you read and then ask who is the editor!!!