Listing New Shareholders

Posted on December 21, 2015December 20, 2015 by Dick Kusleika

I have a list of several hundred stock transactions that consist of purchases, redemptions, transfers, and reissues.

To get a list of new shareholders, I start by creating a pivot table from the data (Alt+N+V+Enter)
Put the ShareholderName in Row Labels and the Date in Values
Right click on any of the date numbers and choose Summarize Values By Min to get the earliest date for every shareholder.
Right click on any of the dates (that probably don’t look like dates) and choose Value Field Settings. Then click on Number Format and format the field as a date.
While you still have a date selected, choose Sort from the PivotTable Tools – Options Ribbon and sort largest to smallest
Now you can copy the 10 new shareholder names from 2015.

Now try it yourself. You can download StockRegister.zip

One thought on “Listing New Shareholders”

or in VBA with a filter:

Sub M_snb()
  With ActiveWorkbook.PivotCaches.Create(1, "tblStock", 4).CreatePivotTable("Sheet4!R1C1", "snb", , 4)
    .ColumnGrand = False
    .AddDataField .PivotFields("Date"), "Mindate", -4139
    With .PivotFields("ShareholderName")
      .Orientation = 1
      .PivotFilters.Add 7, .Parent.PivotFields("Mindate"), [max(sheet4!B:B)]
    End With
  End With
End Sub

Sub M_snb()

With ActiveWorkbook.PivotCaches.Create(1, "tblStock", 4).CreatePivotTable("Sheet4!R1C1", "snb", , 4)

.ColumnGrand = False

.AddDataField .PivotFields("Date"), "Mindate", -4139

With .PivotFields("ShareholderName")

.Orientation = 1

.PivotFilters.Add 7, .Parent.PivotFields("Mindate"), [max(sheet4!B:B)]

End With

End Sub

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Daily Dose of Excel

Haphazardly Posted Excel Information and Other Stuff

Listing New Shareholders

One thought on “Listing New Shareholders”

Leave a Reply