You thought PowerShell was just for managing servers? Think again! Today, we`re going to engage in a noble pursuit: counting other people’s money. And not just anywhere — we’re diving into the official filings of the U.S. Securities and Exchange Commission (SEC). All from the comfort of the console, with a splash of Vega and a hint of analytical mischief.

Our target of curiosity: Form 4, where corporate big shots report their stock transactions:

Why do we care? Well… we’re just curious who dumped their shares right before the price tanked 😉

Pulling data

To do this, we`ll need two trusty PowerShell functions:

function Get-RecentSecForm4XmlUrls {
    param (
        [string]$CIK = "0000789019",
        [int]$DaysBack = 100
    )

    $headers = @{
        "User-Agent" = "PowerShellScript/1.0 (eosfor@gmail.com)"
        "Accept-Encoding" = "gzip, deflate"
    }

    $url = "https://data.sec.gov/submissions/CIK$CIK.json"
    $data = Invoke-RestMethod -Uri $url -Headers $headers

    $cikTrimmed = $CIK.TrimStart("0")
    $cutoffDate = (Get-Date).AddDays(-$DaysBack)

    $results = @()

    for ($i = 0; $i -lt $data.filings.recent.form.Length; $i++) {
        $formType = $data.filings.recent.form[$i]
        if ($formType -ne "4") { continue }

        $filingDate = Get-Date $data.filings.recent.filingDate[$i]
        if ($filingDate -lt $cutoffDate) { continue }

        $accessionNumber = $data.filings.recent.accessionNumber[$i]
        $primaryDoc = $data.filings.recent.primaryDocument[$i]
        $reportDate = $data.filings.recent.reportDate[$i]

        $folder = $accessionNumber -replace "-", ""
        $xmlFileName = [System.IO.Path]::GetFileNameWithoutExtension($primaryDoc) + ".xml"
        $xmlUrl = "https://www.sec.gov/Archives/edgar/data/$cikTrimmed/$folder/$xmlFileName"

        $results += [PSCustomObject]@{
            FilingDate = $filingDate.ToString("yyyy-MM-dd")
            ReportDate = $reportDate
            XmlUrl     = $xmlUrl
        }
    }

    return $results
}

function Convert-Form4XmlToRecord {
    [CmdletBinding()]
    param (
        [Parameter(ValueFromPipeline = $true)]
        [pscustomobject]$InputObject
    )

    process {
        $headers = @{
            "User-Agent" = "PowerShellScript/1.0 (eosfor@gmail.com)"
        }

        try {
            [xml]$doc = Invoke-WebRequest -Uri $InputObject.XmlUrl -Headers $headers -UseBasicParsing
        }
        catch {
            Write-Warning "Download failed: $($InputObject.XmlUrl)"
            return
        }

        $issuer = $doc.ownershipDocument.issuer.issuerName
        $owner = $doc.ownershipDocument.reportingOwner.reportingOwnerId.rptOwnerName
        $ownerRelationship = $doc.ownershipDocument.reportingOwner.reportingOwnerRelationship

        # Get all role flags where value is '1'
        $relationshipProps = ($ownerRelationship | Get-Member -MemberType Properties | Where-Object {
            $ownerRelationship.$($_.Name) -eq "1"
        }).Name

        # Join multiple roles if needed
        $relationship = if ($relationshipProps.Count -gt 1) {
            $relationshipProps -join ";"
        } else {
            $relationshipProps
        }

        # Собираем footnotes в хештаблицу
        $footnotes = @{}
        if ($doc.ownershipDocument.footnotes -and $doc.ownershipDocument.footnotes.footnote) {
            $rawFootnotes = $doc.ownershipDocument.footnotes.footnote
            if ($rawFootnotes -is [System.Array]) {
                foreach ($f in $rawFootnotes) {
                    $footnotes[$f.id] = $f.'#text' ?? $f.InnerText
                }
            } else {
                $footnotes[$rawFootnotes.id] = $rawFootnotes.'#text' ?? $rawFootnotes.InnerText
            }
        }

        $transactions = $doc.ownershipDocument.nonDerivativeTable.nonDerivativeTransaction
        foreach ($txn in $transactions) {
            $note = $null
            if ($txn.footnoteId) {
                $ids = if ($txn.footnoteId -is [System.Array]) {
                    $txn.footnoteId | ForEach-Object { $_.id }
                } else {
                    @($txn.footnoteId.id)
                }
                $note = ($ids | ForEach-Object { $footnotes[$_] }) -join "; "
            }

            [PSCustomObject]@{
                FilingDate              = $InputObject.FilingDate
                ReportDate              = $InputObject.ReportDate
                Issuer                  = $issuer
                InsiderName             = $owner
                InsiderRole             = $relationship
                SecurityTitle           = $txn.securityTitle.value
                TransactionDate         = $txn.transactionDate.value
                TransactionCode         = $txn.transactionCoding.transactionCode
                SharesTransacted        = $txn.transactionAmounts.transactionShares.value
                PricePerShare           = $txn.transactionAmounts.transactionPricePerShare.value
                SharesOwnedAfterTxn     = $txn.postTransactionAmounts.sharesOwnedFollowingTransaction.value
                OwnershipType           = $txn.ownershipNature.directOrIndirectOwnership.value
                IndirectOwnershipNature = $txn.ownershipNature.natureOfOwnership.value
                Footnote                = $note
                XmlUrl                  = $InputObject.XmlUrl
            }
        }
    }
}

📥 Let’s fire up our surveillance script and stash the data in a variable named $allData. Think of it as “doing a background check,” but legally.

# you can put your CIK here :)
$CIKs = "0000789019", "0000320193", "0001318605", "0001288776", "0001352010" # who is that?
$allData = $CIKs | % { Get-RecentSecForm4XmlUrls -CIK $_  -DaysBack ((Get-Date).DayOfYear) } | Convert-Form4XmlToRecord

🧹 Next step — let’s clean house. We only care about transactions where money actually moved. If the number of shares is 0 — skip it. We`re here for the real million-dollar moves (or at least a few solid trades).

$data = $allData |
    Select-Object TransactionDate, SharesTransacted, TransactionCode |
    Where-Object { $_.TransactionCode -in @("S", "P", "F", "A", "M", "G") -and $_.SharesTransacted -gt 0 }

$data = $data | ForEach-Object {
    $action = switch ($_.TransactionCode) {
        "S" { "Sell"; break }
        "F" { "Sell"; break }
        "G" { "Sell"; break }
        "A" { "Buy"; break }
        "P" { "Buy"; break }
        "M" { "Buy"; break }
        default { "Other" }
    }

    $_ | Add-Member -NotePropertyName Action -NotePropertyValue $action -Force -PassThru
}

🔧 Almost forgot! To make all this work smoothly, we had to contribute a little something to dotnet/interactive. Why? Because the CustomMimeType parameter in Out-Display was… well, kind of there but not really working. Now it works — JSON specs right from the notebook cell, beautiful charts and all. Feel free to thank the author of PR #3671, and that’s, actually, me 😉

Scatter plot

To make it more fun, we can also add a bit of interactivity to this page :)

📈 Scatter Plot — our first visual interrogation:

A quick way to spot who knew what and sold just in time 💸

Heat map

🔥 Heatmap — follow the heat to find the insiders:

Anyone can make mistakes — but heatmaps? They never lie. 💼

🔍 TransactionCode meaning

Code What it means How to interpret it
A Award Shares granted, usually a bonus. Like a gift card, but in stock.
S Sale Sold shares. Sometimes en masse. Often… right before a price drop.
F Tax Shares withheld to pay taxes. At least they didn’t keep those.
M Option Exercise Exercised an option. Buy low, sell high — the corporate dream.
G Gift Given away. To family. Or a trust. Or a charity. No judgment here.
P Purchase Bought shares. With their own money. Respect.
I Discretionary Auto-trade via plan. Legit? Depends who you ask.
C Conversion Transformed derivatives into common shares. Totally by the book.

Bubble chart

🔵 Bubble Chart — where every bubble is a trade, and size shows how big it was. The bigger the bubble — the juicier the deal:

Makes it pretty obvious who burst the greed bubble first 😄