Creating Effective Dashboards for Insider Transactions in Excel

A good spreadsheet can make 162,000 filings feel small, at least until someone sorts text as dates.

Insider data has a reputation for being specialised. It is not. Most of the work is clerical, which is good news, because Excel is very good at clerical work when you stop asking it to be a database and let it be a spreadsheet.

This piece is a practical guide to taking a CSV export of insider filings and turning it into something you can actually use. No macros, no Power Query acrobatics, no heroic VBA written by someone who has since left the firm. Just a clean import, a few pivot-table recipes, and sample formulas that reduce the odds of embarrassing yourself in front of a portfolio manager.

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:

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

1. 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.

2. 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.

3. 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())
   

The five-minute setup

The fastest useful workbook has three sheets:

• RawData
• Pivots
• Lookups

That is enough structure to keep things tidy without pretending you are building a data warehouse in a workbook.

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.

Recipe 1, issuer heatmap

This is your triage view.

Rows

• issuer_name

Columns

• year_month

Values

• Sum of signed_value

Filters

• open_market_flag = 1
• market
• insider_role

What it shows: whether a company has a cluster of net buying or net selling over time.

Why it matters: isolated trades can be noise. Repeated net buying by several insiders is harder to dismiss. Not always predictive, but at least interesting.

Make it readable

• Sort rows by the latest month’s signed value.
• Apply conditional formatting with a red-to-green scale.
• Keep the date horizon short, say 6 to 12 months.

If the pivot looks like a Christmas tree after this, your classifications need work.

Recipe 2, insider leaderboard

This one answers who is most active.

Rows

• insider_label

Values

• Sum of transaction_value
• Count of filing_id

Filters

• transaction_code
• transaction_date
• issuer_name

What it shows: which insiders are making the largest trades and whether activity is concentrated in one person or spread across several.

Why it matters: a single director making a small purchase is one thing. The CEO, CFO, and two directors buying in the same month is another.

Add a top-10 filter

Right-click the row labels, apply a Value Filter > Top 10 by sum of transaction value. This is the fastest route to a usable watchlist.

Recipe 3, filing-timeliness monitor

This is the compliance-adjacent pivot.

Rows

• issuer_name

Values

• Average of filing_lag_days
• Max of filing_lag_days
• Count of filing_id

Filters

• market
• year_month

What it shows: how quickly filings appear after the transaction date.

Why it matters: if you monitor multiple jurisdictions or data vendors, lag structure matters. A signal that arrives late is still a signal, but it is no longer fresh.

Sample formulas for a cleaner insider workbook

Pivot tables do the aggregation. Formulas do the housekeeping. You need both.

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:

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:

1. Download the latest CSV.
2. Paste or refresh into RawData.
3. Check row count and date formats.
4. Refresh all pivot tables.
5. Review the issuer heatmap for fresh net buying.
6. Check whether activity is clustered across multiple insiders.
7. Exclude non-discretionary or low-information transaction types.
8. 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.

Why this matters in a spreadsheet

If you compare filing lags across markets, or aggregate transaction types from multiple jurisdictions, you need a normalised taxonomy. Otherwise your pivot table will present a neat answer to a badly posed question. Excel is innocent here. It is merely obedient.

Common mistakes, and how not to make them before lunch

The mistakes are repetitive, which is convenient for an editor and less convenient for analysts.

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:

1. Net insider value by issuer, current month
2. Top insiders by transaction value
3. Monthly net buying trend
4. 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.

Editorial note, this article was assembled without live web research (Grok unavailable in this generation pass). Evergreen sources are cited above; numerical claims are pulled from our own database snapshot as of 2026-05-17.

Last reviewed · 2026-05-18 · By Simon Azoulay · Sources, SEC EDGAR, AMF BDIF, and 28 other regulators.