Transform Insider Filings into Usable Excel Dashboards, Sigma Journal

InsidersTradesSigma

Why Excel still wins the first round

The first pass on insider transactions is usually not a modelling problem. It is a filtering problem. You want to know:

which issuers have recent buying,
whether activity is clustered among several insiders,
whether the trades are open-market purchases or something less informative,
whether filing dates lag transaction dates,
and whether the values are large enough to deserve attention.

That is pivot-table territory.

A decent CSV can answer these questions faster in Excel than in most internal dashboards, largely because Excel lets an analyst improvise. You can drag fields around, test a hypothesis, and abandon it 20 seconds later with no engineering ticket required. This is one of the few places where the old software earns its keep.

The minimum viable insider CSV

If you are downloading data from an internal platform or a vendor, the ideal export includes these columns:

Column	Why it matters
filing_id	Unique key, useful for deduplication
issuer_name	The company you care about
issuer_ticker	Easier grouping and chart labels
insider_name	Identifies the reporting person
insider_role	CEO, director, 10% owner, etc.
transaction_date	The economic event
filing_date	The disclosure event
transaction_code	Purchase, sale, option exercise, gift, and so on
security_type	Common stock, derivative, RSU, etc.
shares	Position size in units
price	Per-share price where applicable
transaction_value	Shares × price, if available
ownership_nature	Direct or indirect ownership
market	US, FR, and so on, if cross-market
currency	Needed if values are mixed

If your export lacks one of these, write n/a into your process notes rather than pretending the field never mattered. Missingness is part of the analysis.

Before you pivot, do three things

Convert the range to a table
Select any cell in the CSV, press Ctrl + T, confirm headers. Name the table something civilised, such as tblInsider.
Fix date columns immediately
Check that transaction_date and filing_date are real dates, not left-aligned text pretending to be dates. If Excel thinks 2026-03-04 is prose, your monthly summary will become abstract art.
Standardise transaction value
If the CSV does not include transaction_value, add it as a calculated column:
```
=[@shares]*[@price]
```
If either field is missing, use:
```
=IFERROR([@shares]*[@price],NA())
```

Step 1, import the CSV cleanly

Open the CSV in Excel, or use Data > From Text/CSV if you want more control over types. Once loaded:

convert to a table,
freeze the top row,
apply filters,
and format shares, price, and transaction_value as numbers.

If your file contains mixed currencies, do not aggregate value fields across currencies unless you have an FX-normalised column. This sounds obvious. It is also how bad charts are born.

Step 2, add helper columns

Helper columns are where Excel goes from tolerable to useful. Here are the ones worth adding.

Signed value

If your transaction codes distinguish buys from sells, create a signed measure so purchases are positive and sales are negative.

=IF(OR([@transaction_code]="P",[@transaction_code]="Buy"),[@transaction_value],
 IF(OR([@transaction_code]="S",[@transaction_code]="Sell"),-[@transaction_value],0))

Adjust the codes to match your source. US Form 4 data often uses P for purchase and S for sale. Other datasets may use plain-English labels.

Year-month bucket

For monthly pivots, a text label is often easier than Excel’s automatic date grouping.

=TEXT([@transaction_date],"yyyy-mm")

If you prefer a real month-end date for sorting and charting:

=EOMONTH([@transaction_date],0)

Filing lag in days

This is useful both operationally and analytically.

=[@filing_date]-[@transaction_date]

Format as a number. If the result is nonsense, your dates are text.

Open-market flag

A simple binary flag helps isolate the part of the dataset that usually carries the most signal.

=IF(OR([@transaction_code]="P",[@transaction_code]="S",[@transaction_code]="Buy",[@transaction_code]="Sell"),1,0)

A more refined version would exclude plan trades or derivative events if your source has those fields.

Insider label

This is not necessary, but it makes pivots easier to read.

=[@insider_name]&" ("&[@insider_role]&")"

Step 3, create your first pivot table

Insert a pivot table from tblInsider onto the Pivots sheet.

The first pivot should answer the blunt question: who is buying what?

Rows

issuer_name

Columns

transaction_code

Values

Sum of transaction_value
Count of filing_id

Filters

transaction_date
insider_role
security_type
market

This instantly tells you whether a company’s recent activity is dominated by purchases, sales, or administrative clutter.

Pivot recipes that actually earn their keep

Most insider-data workbooks become unusable because they try to show everything at once. Better to have three pivots with clear jobs.

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.

Daily or weekly routine

A sensible workflow looks like this:

Download the latest CSV.
Paste or refresh into RawData.
Check row count and date formats.
Refresh all pivot tables.
Review the issuer heatmap for fresh net buying.
Check whether activity is clustered across multiple insiders.
Exclude non-discretionary or low-information transaction types.
Export a short watchlist for deeper research.

This can be done in minutes if the workbook is stable.

What counts as interesting

The exact threshold depends on your universe, but the usual signs of life are:

multiple open-market purchases in the same issuer,
senior officers involved rather than only non-executive directors,
meaningful transaction values relative to salary or prior holdings,
repeated buying over several weeks or months,
and prompt filings that reduce the chance you are looking at stale information.

What does not count as interesting, at least not on its own:

a single tiny purchase,
option exercises with immediate sale-to-cover,
gifts,
routine tax dispositions,
and values aggregated across mixed securities without adjustment.

Add slicers if other people will use the workbook

If the workbook is for a team, slicers make pivot tables less frightening.

Useful slicers:

market
transaction_bucket
insider_role
year_month

They are not necessary. They are, however, a courtesy.

A short note on regulation, because the data is shaped by the rules

Insider datasets are not natural objects. They are products of disclosure regimes. If you use US Form 4 data, European MAR disclosures, or UK PDMR announcements, the fields and timing reflect legal obligations, not an analyst’s preferences.

That matters in Excel because you will be tempted to compare unlike with unlike.

The US baseline

In the United States, insiders subject to Section 16 report many transactions on Form 4, generally within two business days after the transaction. The SEC’s EDGAR system provides the filings, and transaction coding conventions are well established.

The European baseline

In the EU, the Market Abuse Regulation, especially Article 19, governs managers’ transactions disclosures. National competent authorities, such as the AMF in France, supervise the regime locally. The broad idea is similar, but the publication mechanics and source formats vary more than many users expect.

Market	Regulator	Rule	Deadline	Notes
FR	AMF	MAR Art 19	T+3	Managers' transactions are disclosed under the EU Market Abuse Regulation, with local publication practices and issuer announcements shaping data availability.
US	SEC	Exchange Act Section 16, Form 4	T+2 business days	Form 4 transaction codes are standardised and EDGAR provides structured access, which makes spreadsheet workflows relatively painless by regulatory standards.

Disclosure frameworks shape the CSV you end up pivoting.

Mistake 1, summing raw shares across different securities

Ten thousand common shares and ten thousand options are not the same thing. Aggregate transaction_value or a normalised measure where possible, not raw units across mixed security types.

Mistake 2, ignoring direct versus indirect ownership

A trust, spouse account, or controlled entity can matter, but it is not the same as direct ownership. Keep ownership_nature available as a filter.

Mistake 3, treating all sales as bearish

Sales are noisier than purchases. They can reflect diversification, taxes, liquidity needs, or pre-arranged plans. A pivot table cannot infer motive. It can only count.

Mistake 4, letting Excel auto-group dates without checking

Excel’s automatic grouping is convenient until it is wrong. If your dates imported as text, the grouping will fail silently or produce bizarre categories. Build your own year_month field and sleep better.

Mistake 5, forgetting duplicates

If you append new CSVs over time, duplicate filings are common. Keep a filing_id and check for repeats with:

=COUNTIF(tblInsider[filing_id],[@filing_id])

Anything above 1 deserves inspection.

Mistake 6, trusting values without currency context

A €500,000 purchase and a $500,000 purchase are close enough for a rough screen, until they are not. If your file mixes currencies, either convert them or keep the analysis local.

The fastest useful dashboard

If you want one final setup that an analyst can maintain without mutiny, make a dashboard sheet with four objects:

Net insider value by issuer, current month
Top insiders by transaction value
Monthly net buying trend
Average filing lag

Add slicers for market, transaction bucket, and month. Keep the colour scheme restrained. Insider data is already dramatic enough.

A simple dashboard headline formula

If cell B2 contains the selected issuer, you can pull its latest net value with:

=SUMIFS(tblInsider[signed_value],tblInsider[issuer_name],B2,tblInsider[year_month],TEXT(EOMONTH(TODAY(),0),"yyyy-mm"))

And latest filing date with:

=MAXIFS(tblInsider[filing_date],tblInsider[issuer_name],B2)

This gives the dashboard a little pulse without requiring anything fancier than worksheet formulas.

If you have 60 more minutes

After the five-minute version works, the next sensible upgrade is Power Query for repeatable imports and code mapping. Not because it is glamorous, but because copying and pasting CSVs forever is how process risk sneaks into simple tasks.

That said, the five-minute version is enough to get from raw filings to a shortlist of names worth reading about. Which is the point.

The payoff is simple: a clean Excel workbook can turn insider filings from an archive into a monitor. The concrete next step is to build one with your own export, then test a single question on it, for example, whether clustered open-market buying by senior officers in the last 30 days surfaces names your current process misses. If it does, you have a tool. If it does not, the open question is not whether Excel failed, but whether your transaction taxonomy did.