Covariantiematrix in Excel

Covariantiematrix in Excel

De covariantiematrix is ​​een vierkante matrix om de covariantie tussen de kolommen en de variantie in kolommen weer te geven. Excel gepresenteerd met ingebouwde 'Data-analyse'-tool om de covariantie tussen de verschillende datasets te bepalen. In dit artikel wordt de berekening van de covariantiematrix in Excel uitgelegd door de volgende onderwerpen te behandelen, waaronder

Uitleg

Covariantie is een van de maatregelen die worden gebruikt om te begrijpen hoe een variabele wordt geassocieerd met een andere variabele. De volgende formule wordt gebruikt om de covariantie te bepalen.

COV (X, Y) = ∑ (x - x) (y - y) / n

De covariantiematrix is ​​een vierkante matrix om de relaties tussen de verschillende variabelen in een dataset te begrijpen. Het is gemakkelijk en handig om de covariantie tussen twee of meer variabelen te laten zien.

De covariantie heeft zowel positieve als negatieve waarden. Een positieve waarde geeft aan dat twee variabelen in dezelfde richting zullen afnemen of toenemen. Een negatieve waarde geeft aan dat als een variabele afneemt, de andere variabele toeneemt en er een omgekeerde relatie tussen bestaat. De covariantiematrix wordt weergegeven in het volgende formaat. De driedimensionale covariantiematrix wordt weergegeven als

Om de 3 × 3 vierkante covariantiematrix te maken, hebben we driedimensionale gegevens nodig. De diagonale waarden van de matrix vertegenwoordigen de varianties van X-, Y- en Z-variabelen (dwz COV (X, X), COV (Y, Y) en COV (Z, Z)). De covariantiematrix is ​​symmetrisch ten opzichte van diagonaal. Dit geeft aan dat COV (X, Y) = COV (Y, X), COV (X, Z) = COV (Z, X) en COV (Y, Z) = COV (Z, Y). Een punt om te onthouden over deze matrix is ​​het resultaat van de NXN-covariantiematrix voor gegevens van n-dimensionaal.

Hoe gebruik je een covariantiematrix in Excel?

De covariantiematrix wordt gebruikt in verschillende toepassingen, waaronder

  • Analyseren hoe twee vectoren met elkaar differentiëren
  • Gebruikt bij machine learning om de afhankelijkheidspatronen tussen de twee vectoren te bepalen
  • De covariantiematrix wordt gebruikt om de relatie tussen de verschillende dimensies van willekeurige variabelen te vertellen
  • Gebruikt in stochastische modellering in financiële engineering om de willekeurige variabelen te correleren
  • Principle Component is een andere toepassing van covariantiematrix op originele variabelen op lineaire onafhankelijke variabelen
  • Bij data-analyse speelt de covariantiematrix een cruciale rol.
  • De covariantiematrix wordt in de moderne portefeuilletheorie gebruikt bij het inschatten van risico's.
  • De maatstaven van de covariantiematrix worden gebruikt om te anticiperen op het rendement op de financiële activa

Voorbeelden van Covariance Matrix in Excel

Hieronder staan ​​enkele voorbeelden om de covariantiematrix in Excel te gebruiken.

U kunt deze Covariance Matrix Excel-sjabloon hier downloaden - Covariance Matrix Excel-sjabloon

Voorbeeld 1

Uitvoeren van covariantie-analyse op de cijfers behaald door studenten in verschillende vakken.

Stap 1: De volgende gegevens, inclusief de cijfers van studenten in wiskunde, Engels en natuurwetenschappen, worden beschouwd als weergegeven in de afbeelding.

Stap 2: Ga naar het tabblad "Gegevens" op het lint en zoek de ToolPak voor "Gegevensanalyse" in de rechterhoek.

Als ToolPak "Gegevensanalyse" niet beschikbaar is, volg dan deze stappen

Stap A: Ga naar het tabblad 'Bestand' en selecteer de “opties”.

Het volgende scherm wordt geopend.

Stap B: Ga naar invoegtoepassingen. Zorg ervoor dat onder de optie Beheren 'Excel-invoegtoepassingen' is geselecteerd en selecteer de knop 'Ga' zoals weergegeven in de afbeelding.

Stap C: Selecteer de "Analysis-Tool Pak" en "Analysis-ToolPak VBA" zoals weergegeven in de schermafbeelding.

Na het voltooien van deze stappen, wordt het toolpakket "Gegevensanalyse" toegevoegd aan het tabblad "Gegevens".

Stap 3: Klik op Gegevensanalyse. Dit opent het dialoogvenster "Gegevensanalyse". Selecteer de "Covariantie" door omhoog te scrollen en klik op "OK".

Het toont het dialoogvenster "Covariantie".

Stap 5:  Selecteer het invoerbereik inclusief de onderwerpnamen, vink de “labels in eerste rij” aan en geef het “uitvoerbereik” in het bestaande werkblad. En klik op "OK".

Stap 6:  We krijgen de uitvoer als volgt -

Het bovenste deel van de diagonaal is leeg omdat de Excel-covariantiematrix symmetrisch is naar de diagonaal toe.

Voorbeeld # 2

Berekening van de covariantiematrix om de varianties te bepalen tussen de rendementen van verschillende portefeuilleaandelen.

Stap 1: Voor dit voorbeeld wordt rekening gehouden met de volgende gegevens, inclusief de voorraadretouren.

Stap 2: Opent het dialoogvenster "Gegevensanalyse" en selecteer de "Covariantie" door omhoog te scrollen en op "OK" te klikken.

Het toont het dialoogvenster "Covariantie".

Stap 3: Selecteer het invoerbereik inclusief de kopteksten, vink de "labels in eerste rij" aan en geef het "uitvoerbereik" op in het bestaande werkblad. En klik op "OK".

Stap 4: We krijgen de uitvoer als volgt -

Het bovenste deel van de diagonaal is leeg omdat de covariantiematrix symmetrisch is naar de diagonaal toe.

Voorbeeld # 3

Berekening van covariantiematrix voor aandelenkoersen van bedrijven

Stap 1: Voor dit voorbeeld worden de volgende gegevens, inclusief aandelenkoersen van verschillende bedrijven, in aanmerking genomen.

Stap 2: Opent het dialoogvenster "Gegevensanalyse", selecteer de "Covariantie" door omhoog te scrollen en klik op "OK".

Het toont het dialoogvenster "Covariantie".

Stap 3: Selecteer het invoerbereik inclusief de kopteksten, vink de “labels in eerste rij” aan en geef het “uitvoerbereik” in het bestaande werkblad en klik op “OK”.

Stap 4: We krijgen de uitvoer als volgt -

Dingen om te onthouden

  • De covariantietool die door Excel wordt gepresenteerd, heeft enkele beperkingen, waaronder de bepaling van alleen de formule voor populatieverschillen, het maken van een matrix met alleen lagere diagonale waarden en het overwegen van formules alleen voor een variantie.
  • Wanneer retourwaarden worden gewijzigd, worden de waarden van de matrix niet automatisch bijgewerkt.
  • De bovenste helft van de matrix wordt weergegeven als leeg omdat deze symmetrisch is en spiegelbeeldwaarden worden weergegeven in de onderste diagonaal.