Spara tid vid portföljsammanställningar!

Excel_Sheets.jpg

Efter att ha läst ett blogginlägg av @SnackarGoja där han delade med sig av lite knep i Excel så blev jag inspirerad att själv visa några av de metoder jag använder mig av i Excel/Google Sheets för att spara tid. Jag använder mig delvis av kvantitativa strategier för att inte behöva lägga så mycket tid på mitt investerande men samtidigt känna mig trygg i att ändå prestera bra på lång sikt. Samtidigt redovisar jag mina resultat månadsvis och måste då uppdatera illustrationerna på resultatsidan varje gång, något som riskerar att bli väldigt tidskrävande. Hur får jag dessa två påståenden att gå ihop?

Jag tänker ge exempel på tre saker som spar mycket tid för mig varje månad:

 

1. Automatisk uträkning av avkastning med Google Sheets/Google Finance

Det första tipset är hur jag på ett ganska automatiserat sätt kan få månadsavkastningen uträknad för enskilda aktier, även om de har gett utdelning (som då inte räknas med i avkastningen för den aktien på nätmäklarens sida).

Till att börja med så sköter jag all bevakning av portföljerna i Google Sheets. Eftersom jag automatiskt kan hämta aktiekurser från Google Finance så behöver jag inte göra annat än att gå in på dokumentet för att se utvecklingen. Nu tänker ju flera av er att det går lika bra att logga in på sin nätmäklare och där se hur de olika bolagen har utvecklats. Om jag bara skulle vara intresserad av den rena kursutvecklingen så skulle det räcka ganska långt. Men i mina månadsrapporter måste utvecklingen för respektive innehav vara inklusive utdelningar. Som exempel hade bolagen i min Trending Value-strategi utvecklats så här under de första fyra månaderna (mars-juni):

Excel-tips_2.png

Alla bolag (utom KappAhl) har gett utdelning under den tiden som jag har haft dem och jag har därför räknat in det beloppet i den totala utvecklingen. För att göra detta använder jag mig av två tabeller i Google Sheets. Allt jag behöver göra är att föra in utdelningar i den ena av dem.

Excel-tips_3_1.png

Motsvarande cell i den andra tabellen använder sedan utdelningen, mitt GAV och GAV-kurs samt stängningskursen den sista börsdagen den månaden för att räkna fram den totala utvecklingen.

Excel-tips_5_1.png

Sedan behöver jag bara kopiera över den senaste månadens utveckling i det Excel-dokument jag använder som layout för mina rapporter och så är det klart! Har det inte kommit in några utdelningar behöver jag inte föra in ens det, utan bara kopiera över värdena från Google Sheets till Excel. Formeln för att räkna fram utvecklingen ser ut som i bilden nedan.

Excel-tips_4.png

Utdelningarna ligger alltså i ett annat blad som heter “Utdelningar/Övrigt”. Jag har valt att låta den göra uträkningen direkt efter den sista börsdagen istället för att sätta datumet till den sista i varje månad. Om månaden avslutas med en lördag eller söndag så vill jag oftast inte vänta till efter helgen med att sammanställa månadens resultat. Det är alltså först efter det datum som står på rad 22 som “#REF !” byts ut mot riktiga värden. Det här tipset är mest användbart för personer som ombalanserar sällan och inte ändrar eller fyller på bland sina innehav så ofta.

Jag har skapat en mall för detta som finns i länken nedan. Efter att ni har kopierat över den så kan ni testa med era egna innehav. För tillfället lade jag bara till de 12 första bolagen på Stockholmsbörsen efter bokstavsordning.

Värdeförändring_Mall

OBS!
Att automatiskt kunna hämta data från Google Finance till Google Sheets är en fantastisk funktion, men tyvärr väldigt opålitlig. Google Sheets har en tendens att strula när den försöker hämta värden från Google Finance (latency issues), så det krävs ibland att man uppdaterar dokumentet (F5) eller öppnar fliken på nytt för att få alla celler att hämtas korrekt (något man ibland måste göra sjuttioelva gånger innan det fungerar…). Ju fler formler som försöker hämta kurser eller annan finansiell data, desto större risk verkar det vara att den avbryter hämtningen och ger felmeddelandet “#REF !” istället. Därför är nästa tips inte med i mallen, men jag visar ändå formeln ifall någon vill pröva lyckan och lägga till den.

 

2. SUMPRODUCT (PRODUKTSUMMA)

Det andra tipset är hur jag räknar fram portföljens månadsutveckling och den totala utvecklingen sedan jag ombalanserade portföljen. Även det görs automatiskt med hjälp av formeln SUMPRODUCT. Den multiplicerar två kolumner med varandra och summerar sedan värdena. I det här fallet multipliceras avkastningen för varje enskild aktie med dess portföljvikt, varpå dessa summeras. Det ser ut som i bilden nedan.

Sumproduct_4

Det här är bara ett exempel på hur det ser ut om det görs för två månader. I mina ark görs denna uträkning varje månad, tillsammans med två ytterligare rader som visar månads- och totalavkastningen för OMXSGI för motsvarande period.

 

3. LOOKUP (LETAUPP)

När jag väl har alla siffror över hur det har gått den senaste månaden behöver jag lägga över dem i Excel för att kunna exportera månadsrapporterna. Jag kopierar över månads- och totalavkastningarna för min portfölj och för OMXSGI. Min mall i Excel använder sedan formeln LOOKUP för att hämta det sista värdet i de kolumner som jag har klistrat in dem i. Sedan uppdateras fälten i bilderna nedan med de senaste värdena. Kolumn AC och AD är alltså de kolumner där jag klistrar in den totala avkastningen för min portfölj och för OMXSGI sedan jag ombalanserade strategin. Kvar återstår sedan bara att exportera rapporten och lägga upp den på bloggen!

Excel-tips_7.png

Excel-tips_6.png

 

Slutligen vill jag tipsa om en sida jag använder mig av ibland för att ta reda på vad formler i Google Sheets/Excel heter på olika språk. Det finns mycket mer support på engelska så jag söker alltid på engelska termer när jag har kört fast i Excel.

http://dolf.trieschnigg.nl/excel/index.php?langids=en+se

 

Mycket nöje!

Leave a Reply

Your email address will not be published. Required fields are marked *