XLOOKUP for code mapping
Suppose your raw data has transaction codes in column transaction_code, and you maintain a mapping table in Lookups with columns code and bucket.
Example mapping:
| code |
bucket |
| P |
Open-market buy |
| S |
Open-market sell |
| M |
Option exercise |
| G |
Gift |
| F |
Tax withholding |
Use:
=XLOOKUP([@transaction_code],Lookups!A:A,Lookups!B:B,"Unmapped")
This gives you a clean transaction_bucket field for pivots.
If you see Unmapped, good. That means the workbook is telling you the truth.
Flag multiple insiders buying the same issuer
A useful screen is whether more than one insider bought the same issuer in the same month.
If you have issuer_name, year_month, insider_name, and a buy flag, a simple count can help. In a helper column:
=COUNTIFS(tblInsider[issuer_name],[@issuer_name],tblInsider[year_month],[@year_month],tblInsider[transaction_code],"P")
If your source uses labels instead of codes, adjust accordingly.
A more refined version counts distinct insiders, which modern Excel can handle with dynamic arrays, though not elegantly inside every table setup. On a separate sheet, you can use:
=COUNTA(UNIQUE(FILTER(tblInsider[insider_name],(tblInsider[issuer_name]=A2)*(tblInsider[year_month]=B2)*(tblInsider[transaction_code]="P"))))
That is a mouthful, but it works.
Last filing per issuer
If you want a quick recency check:
=MAXIFS(tblInsider[filing_date],tblInsider[issuer_name],[@issuer_name])
Useful for a coverage sheet that lists each issuer and the most recent filing date.
Net value by issuer
For a summary table outside pivots:
=SUMIFS(tblInsider[signed_value],tblInsider[issuer_name],A2)
Where A2 contains the issuer name.
Average purchase price for buys only
=SUMIFS(tblInsider[transaction_value],tblInsider[issuer_name],A2,tblInsider[transaction_code],"P")/SUMIFS(tblInsider[shares],tblInsider[issuer_name],A2,tblInsider[transaction_code],"P")
Wrap in IFERROR if needed:
=IFERROR(SUMIFS(tblInsider[transaction_value],tblInsider[issuer_name],A2,tblInsider[transaction_code],"P")/SUMIFS(tblInsider[shares],tblInsider[issuer_name],A2,tblInsider[transaction_code],"P"),NA())
Month-over-month change in net insider value
If you have a monthly summary table with issuer in rows and month columns, a simple subtraction works. If you prefer formula-driven summaries:
=SUMIFS(tblInsider[signed_value],tblInsider[issuer_name],A2,tblInsider[year_month],B$1)-SUMIFS(tblInsider[signed_value],tblInsider[issuer_name],A2,tblInsider[year_month],TEXT(EOMONTH(DATEVALUE(B$1&"-01"),-1),"yyyy-mm"))
This is the sort of formula that makes one nostalgic for SQL. Still, it gets the job done.
A practical workflow for monitoring insider activity
The point of the workbook is not to admire its architecture. It is to make a decision about what deserves a second look.