Automatisera ditt kalkylark med GoogleSheets Del 2 (En kombinerad köphistorik)

Det går inte många timmar på ett dygn innan man läser något som får en att reflektera över hur konstigt samhället är. Vissa människor, och specifikt miljö-fascister och miljö-terrorister, får gång på gång begå brott utan att bli dömda som de brottslingar de faktiskt är. De kommer undan med att limma sig fast på flygplan, ta sig in på områden med tydligt märkta skyltar med både inträdesförbud och fotoförbud, kedja fast sig i träd och förstöra människors livsinkomst, åka med fartyg till krigsförande nationer (mot UDs inrådan) och bli hemflugna av regeringen.

Enligt svensk definition på terrorism, d.v.s utför man dessa handlingar kan man inte blunda för att man är en terrorist, så lyder den:

"Enligt denna definition är terroristbrott handlingar, till exempel mord, dråp, människorov, sabotage eller kapning, som allvarligt kan skada en stat eller mellanstatlig organisation om de syftar till att:

• Injaga allvarlig fruktan hos en befolkning eller befolkningsgrupp.
• Tvinga offentliga organ eller en mellanstatlig organisation att vidta eller avstå från att vidta en åtgärd.
• Destabilisera eller förstöra grundläggande politiska, konstitutionella, ekonomiska eller sociala strukturer."

Inte konstigt att samhället håller på att fallera när dessa människor står över lagen, samtidigt som motsvarande aktioner i annat syfte än klimatfrågor resulterar i stränga domar och långa fängelsestraff. Det är skillnad på terrorister och terrorister när de borde vara helt lika inför lagen.

 Det är tyvärr inget nytt, i alla tider har pengar köpt människor fördelar med olika typer av lösningar. Nog om det och vidare till Del 2 i hur du skapar ett automatiserat kalkylark för dina aktier.

 

Skapa en flik med din köphistorik

I det förra inlägget så visade jag ett exempel på hur du kan kombinera alla dina transaktioner så du får en samlad bild av alla i en flik. Det är grunden för att kunna bearbeta datat i övriga flikar. Med grunden stabil så är det dags att skapa en flik för alla dina "Köptransaktioner".

Det innebär att vi också måste resonera lite kring vad som faktistk är ett "Köp" och varför. De allra flesta tänker nog att när man spenderar pengar på något så är det ett KÖP, och motsvarande när man säljer något så är det SÄLJ. Inte riktigt lika enkelt när man har en lång historik med olika varianter av "köp" och sälj" transaktioner.

 

Därför måste vi hantera alla köp, samt definiera dem i en egen flik så att vi senare kan justera varje del av kalkylarket individuellt om vi behöver justera andra flikar framöver.

 

Definiera sedan vad ett köp är

Tänk på att ett KÖP kan se ut på flera olika sätt och jag har valt att inkludera dessa transaktionstyper i min historik som ett köp:

• KÖPT (självklart, det här är när vi lägger en order och köper något)
• EM INLÄGG VP (det här är när man önskar teckna något, exempelvis IPO)
• INLÄGG FISSION (när man får inlösen aktier av split)
• TILLDELNING INLÄGG (exempelvis tilldelning teckningsrätter som går att sälja)
• INLÄGG VP (skulle kunna vara flytt av värdepapper, eller namnbyte)
• BYTE INLÄGG VP (skulle kunna vara flytt av värdepapper, eller namnbyte)
• UTDELNING INLÄGG VP (man får ett tvingande köp via utdelning av annat bolag)
• TECKNING LIKVID (när du tilldelats en teckningsrätt och du får tilldelning av aktien)

 

Beroende på hur din transaktionshistorik ser ut kanske du behöver ha andra typer av transaktioner här. Eftersom jag uteslutande handlar på Nordnet så är det ovan transaktionstyper jag har identifierat som ett "KÖP".

 

Syftet är alltså att sammanställa exakt vilka transaktioner jag klassificerar som ett KÖP i en och samma flik, så att jag i framtiden kan göra beräkningar från denna fliken.

 

 Skapa sedan en formel som hämtar all köphistorik

Här behöver vi skapa en formel som hämtar de transaktioner vi definierar ovan, men vi behöver också hämta ovan transaktioner under specifika omständigheter. Först och främst så skall formeln utgå från den kompletta transaktionshistoriken så vi int emissar något. D.v.s vi hämtar ALL data från fliken "Kombinerad_Transaktionshistorik" som vi skapade i Del 1.

 

Det finns flera sätt att lösa detta på men jag kommer att göra såhär:

• Jag kommer hämta data från flik "Kombinerad_Transaktionshistorik" och använda "Regular Expressions" för att matcha de enklaste transaktionerna som inte har något beroende.
• Sedan nestlar jag nästa villkor i en annan "Regular Expression" som matchar villkoret.
• Och repeat för de beroenden/speciella villkoren jag har.
• Det läggs "enkelt" in med en vanligt formel som sorteras och filtreras. 

 

1.  För att bryta ut punkterna ovan så börjar jag med att välja fliken "Kombinerad_Transaktionshistorik", alla kolumner och alla värden, sorterar den enligt original historiken så att man lätt kan följa, filtrerar den på mina villkor och kombinerar detta med "Regular Expressions".

 

Vi börjar med den enkla delen där vi skall fånga upp alla transaktioner som inte har några beroenden i mina köp:

KÖPT
TECKNING LIKVID
INLÄGG VP
TILLDELNING INLÄGG
INLÄGG FISSION
UTDELNING INLÄGG VP

Dessa har inga beroenden eftersom jag helt enkelt inte behöver göra något annat än att leta efter EXAKT dessa värden i fliken Kombinerad_Transaktionshistorik. Jag letar i en flik som heter "Transaktionstyp" där dessa texter skall förekomma.

 

Formeln för det är ganska enkel:

1 2	={Kombinerad_Transaktionshistorik!A1:AD1; SORT(FILTER(Kombinerad_Transaktionshistorik!A2:AD; (REGEXMATCH(Kombinerad_Transaktionshistorik!F2:F; "^(KÖPT|TECKNING LIKVID|INLÄGG VP|TILLDELNING INLÄGG|INLÄGG FISSION|UTDELNING INLÄGG VP)$"))

 

Notera: Ovan formeln kommer inte fungera utan resten av formeln eftersom det är ett nestlat villkor där vi sorterar och filtrerar flera villkor.

 

2. Nu kommer nästa villkor i FILTER från formeln ovan där jag väljer ut transaktionstypen "EM INLÄGG VP" fast endast om också Nordent specificerade detta med transaktionstexten "ÖNSKAR TECKNA". Detta gör att jag fångar upp när jag ansökt om att teckna värdepapper. Formeln byggs på med detta:

 

1 2	+ ((Kombinerad_Transaktionshistorik!F2:F="EM INLÄGG VP")*(REGEXMATCH(Kombinerad_Transaktionshistorik!W2:W; "ÖNSKAR TECKNA")))

 

 3. Det sista villkoret i mitt FILTER blir att välja ut transaktionstypen "BYTE INLÄGG VP" fast endast om också Nordnet specificerade detta med transaktionstexten "SPLIT eller i kombo med INLÖSEN". Den här är lite knepig eftersom vi kan få nya aktier vid en split, eller via inlössen, eller vid en split med inlösen. Här måste vi fånga upp alla kombinationer och det är därför Regular Expressions är ett enkelt sätt att hantera det. Vi bygger på formeln med detta:

 

1 2	+ ((Kombinerad_Transaktionshistorik!F2:F="BYTE INLÄGG VP")*(REGEXMATCH(LOWER(Kombinerad_Transaktionshistorik!W2:W); "split.*inlösen")))

 

 Nu är formeln nästan komplett och det sista vi behöver göra är bara att avsluta det första villkoret med sorteringen av filtreringen:

 

1	); 7; TRUE; 2; FALSE)}

 

Vi sorterar efter Kolumn 7 (Värdepapper namn) följt av Kolumn 2 (Datum) vilket gör att i Köphistoriken kan man nu följa respektive värdepapper och dess fullständiga KÖP-historik. TRUE/FALSE betyder "Stigande/Fallande". Nu är formeln komplett och kommer att fungera att klistra in, såhär ser den alltså ut helt komplett:

1 2 3 4 5 6 7

={Kombinerad_Transaktionshistorik!A1:AD1; SORT(FILTER(Kombinerad_Transaktionshistorik!A2:AD; (REGEXMATCH(Kombinerad_Transaktionshistorik!F2:F; "^(KÖPT|TECKNING LIKVID|INLÄGG VP|TILLDELNING INLÄGG|INLÄGG FISSION|UTDELNING INLÄGG VP)$")) + ((Kombinerad_Transaktionshistorik!F2:F="EM INLÄGG VP")*(REGEXMATCH(Kombinerad_Transaktionshistorik!W2:W; "ÖNSKAR TECKNA"))) + ((Kombinerad_Transaktionshistorik!F2:F="BYTE INLÄGG VP")*(REGEXMATCH(LOWER(Kombinerad_Transaktionshistorik!W2:W); "split.*inlösen"))) ); 7; TRUE; 2; FALSE)}

 Eftersom formeln är byggd för att fungera helt utan någon strukturering så kommer vi att lyfta in alla kolumner exakt så som de är döpta i originalfliken (Kombinerad_Transaktionshistorik).

 Det är relativt lätt att justera ovan formel och kombinera i olika riktningar beroende på behov, men om du tänker följa den här "enkla" guiden framöver så bör de finnas kvar då andra formler som hanterar GAV m.m. kommer att vara beroende på dessa transaktioner. 

I nästa del går vi igenom hur vi bygger en ny flik för Kombinerad_Säljhistorik baserat på all tillgänglig historik. 

Automatisera ditt kalkylark med GoogleSheets Del 1 (En kombinerad transaktionshistorik)

Det var ett bra tag sedan jag skrev någonting här av olika anledningar. Utan att gå in på detaljer så har tiden spenderats med att bekämpa demokratins förfall efter alla horribla pandemi hitte-på-lagar som alla regeringar världen över tycks försvara. Människor är bra dumma som går på vilken dynga som helst som kommer från politiker.

Vi har nu byggt ett samhälle där det är fritt fram att trakassera valfri åsikt eller valfritt avvikande beteende med en digital stämpel (det s.k vaccinpasset som bara oerhört korkade människor tror att syftet är vad det sagts) som bestämmer om du är välkommen eller inte. Krydda detta med att alla flygplatser bygger ut en kontrollzon för Europa med automatiskt scanning av denna dynga till digital stämpel, och lägg på skräpet Chat Control i mixen så borde poletten trilla ner för de flesta vad det egentliga syftet var.

Skapa en flik med dina transaktionshistoriker

Men nog om det, inlägget handlar främst om hur man kan sätta ihop ett kalkylark som automatiserar det mesta du kan behöva analysera i din portfölj. Det enda du behöver göra är i princip exportera hela din transaktionshistorik från din depå, och strukturera upp datat i ett kalkylark.

Den här bloggen använder Googles verktyg för det, men fungerar lika bra i Excel (förmodligen bättre) om man nu hellre vill använda det.

Det första du behöver göra är att helt enkelt skapa flikar i ditt kalkylark som innehåller all din transaktionshistorik. Jag rekommenderar starkt en flik per depå, och definitivt en flik per bank. Syftet är att separera olika datastrukturer i olika flikar.

Eftersom jag använder nordnet så har jag döpt min flik till: Nordnet_Transaktioner. Där har jag bara importerat dem rakt av, en flik per depå.

Skapa sedan en Kombinerad_Transaktionshistorik flik

Syftet med denna fliken är att kombinera alla dina andra flikar till en stor lång transaktion, lite beroende på hur många transaktioner du har kanske du också behöver skapa flera sådana här flikar. Jag har märkt att när man närmar sig 100k+ rader i ett kalkylark kan det börja bli lite jobbigt att räkna igenom, skapa i.s.f bara flera flikar och dela upp det.

1	=QUERY({'Nordnet_Transaktioner_1'!A:AD; 'Nordnet_Transaktioner_2'!A2:AD}; "SELECT * WHERE Col1 IS NOT NULL ORDER BY Col2 DESC"; 1)

Eftersom i mitt fall ovan har båda flikarna samma datastruktur, då behöver man inte göra särskilt mycket med det. Man behöver egentligen bara säga till att flikarna skall kombineras med en enkel Query. Har du olika datastrukturer så behöver man en mycket mer avancerad formel som strukturerar datat på ett gemenesamt sätt, men klarar av att läsa in så många käll-flikar som det behövs. 

Här är ett exempel på en sådan formel som du kan bygga vidare på efter eget behov om så behövs:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

=QUERY( { {"Datum", "Värdepapper", "Antal", "Kurs", "Källa"}; QUERY(Transaktioner_BankA!A2:D; "SELECT A, B, C, D, 'BankA' WHERE A IS NOT NULL"; 0); QUERY(Transaktioner_BankB!A2:D; "SELECT B, A, D, C, 'BankB' WHERE B IS NOT NULL"; 0); QUERY(Transaktioner_BankC!A2:C; "SELECT A, B, NULL, C, 'BankC' WHERE A IS NOT NULL"; 0) }; "SELECT * WHERE Col2 IS NOT NULL ORDER BY Col1 DESC"; 1 )

Formeln är ganska lik den enklare varianten men vi behöver bara sortera datat så som vi vill ha den, den här behöver såklart byggas på till säkerligen runt 25-30 olika kolumner och rubriker för att den skall fungera från en riktig bank.

En enklare förklaring är följande:
-Yttre QUERY används istället för FILTER för att vi vill kunna strukturera upp datat korrekt, direkt därefter väljer vi i vilken ordning vi att rubrikerna skall genereras.
-Inre QUERY används för att välja rätt kolumner från käll-fliken så att de matchar rubrikerna vi vill att de skall landa under i Yttre QUERY-förfrågan. Det är ganska straight-forward, bara byt ut "SELECT" delen ovan till den ordningen den är i källdatan så kommer den placeras i rätt rubrik i kombinerade fliken.
-Den sista raden tillhör vår Yttre QUERY och ser till att sortera datat efter Kolumn1 vilket är Datum i vårt fall men endast om Värdepapper kolumnen har ett värde.

Det här bör såklart göras med andra filter och andra kontroller men syftet var bara att visa hur man kombinerar olika flikar med olika datastrukturer till en kombinerad flik.

Syftet med den kombinerade fliken är att vi samlar alla transaktioner oavsett sort, oavsett källa...i ett och samma format... så att vi kan bearbeta den korrekt i kommande flikar!

I nästa del går vi igenom hur vi bygger en ny flik för Kombinerad_Köphistorik baserat på all tillgänglig historik.