Innehållsförteckning
Uppdatering 2019-03-18
Jag har gjort en välbehövd omarbetning av det här inlägget. Trots att det är ett av mina äldsta inlägg märker jag att det fortfarande är ett av de inlägg som drar flest besökare. Det gör det extra olyckligt att det nuförtiden är ganska inaktuellt, med länkar som inte längre fungerar m.m.
Jag har därför gjort om hela inlägget. De flesta tidigare tips finns kvar, men nu tillsammans med ytterligare matnyttiga saker. Jag har även modifierat den mall jag tidigare delade med mig av. Nu består den dels av det tidigare arbetssättet (för den som har nytta av det). Dessutom är den utökad med ett liknande sätt att sammanställa allt på, ett sätt som ligger nära det jag själv använder mig av i dagsläget.
Efter att ha läst ett inlägg på @SnackarGojas numera nedlagda blogg, där han delade med sig av lite knep i Excel, 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. Till viss del investerar vi i kvantitativa strategier för att inte behöva lägga så mycket tid på vårt investerande, men ändå känna oss trygga i att prestera bra på lång sikt. Samtidigt redovisar jag våra resultat månadsvis, och måste då uppdatera rapporterna jag visar på resultatsidan varje gång. Det riskerar att bli väldigt tidskrävande, så hur får jag dessa två påståenden att gå ihop?
Jag tänker ge exempel på flera saker som spar mycket tid för mig varje månad:
Mallen till allt jag skriver om finns här, men återkommer också längre ned i inlägget efter beskrivningarna.
Portföljsammanställning_AV_Mall
Automatisk uträkning av avkastning med Google Sheets / Google Finance
Det mesta av det här är skräddarsytt efter hur jag håller reda på våra siffror (med bland annat årlig rebalansering). Däremot kan det finnas inspiration att hämta även för de som investerar på andra sätt.
Metod 1 (tidigare arbetssätt)
All bevakning av utvecklingen görs i Google Sheets. Eftersom jag automatiskt hämtar aktiekurser från Google Finance behöver jag inte göra annat än att gå in på dokumentet för att se utvecklingen. Att automatiskt kunna hämta data från Google Finance ä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 (latency issues), så det krävs ibland att man uppdaterar dokumentet (eller provar igen vid ett senare tillfälle) innan alla celler har hämtats korrekt. 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.
Med allt detta strul tänker du säkert att det borde gå 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. Det visas inte i totalavkastningen för enskilda aktier, oavsett vilken tjänst du använder.
För att ta ett extremt exempel gjorde ett av mina amerikanska innehav, Warrior Met Coal, en extrautdelning den 20/4 2018, motsvarande en DA på ca 22 %. På x-dagen sjönk följaktligen kursen ungefär lika mycket. Utan att själv lägga ihop dessa siffror får jag helt felaktig historik för alla bolag som någon gång lämnar utdelning.
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.
Motsvarande cell i den andra tabellen hämtar sedan utdelningen och vårt genomsnittliga anskaffningsvärde (GAV) samt stängningskursen den sista kalenderdagen den månaden för att räkna fram den totala utvecklingen.
Formeln för att räkna fram utvecklingen ser ut som i bilden nedan.
Den första delen av formeln (IF och NOW) är bara med så att bladet inte visar några värden förrän hela månaden är förbi. Utan de villkoren kommer det stå “#REF !” i alla celler som fortfarande ligger i framtiden (vilket helt enkelt är fult).
I den gamla mallen gjordes uträkningen direkt efter den sista börsdagen varje månad (istället för den sista kalenderdagen). Däremot kan det fortfarande bli valutaförändringar trots att börsen inte är öppen, vilket gör att siffrorna inte blev exakta för bolag utanför Sverige. Jag har därför nöjt mig med att låta den göra uträkningarna först när hela månaden är förbi.
Tidigare kopierade jag sedan över den senaste månadens utveckling i det Excel-dokument jag använder som layout för mina rapporter, så var det klart! (Nästan)
Metod 2 (nuvarande arbetssätt)
Varför har jag valt att ändra mitt sätt att sammanställa vår avkastning på? Även om jag genom det här sättet automatiskt fick resultat för respektive bolag och portföljen som helhet, gjorde jag sedan många andra uppdateringar manuellt. Jag skrev rätt månad, räknade fram skillnaden mellan oss och vårt jämförelseindex (alfa), avkastningen sedan starten av strategin istället för bara den senaste rebalanseringen m.m. Det är därför jag sedan vidareutvecklade mallen för att landa i vad vi istället använder idag.
Nackdelen med den tidigare metoden var även att den hela tiden tvingade Google Sheets att räkna fram alla tidigare månaders utveckling varje gång dokumentet öppnades/uppdaterades (som efter 12 månader med 12 bolag blir 144 anrop till Google Finance…).
Den stora skillnaden i det nya sättet är att den nu endast visar en månad i taget. Genom att begränsa mig till en serie värden som ska hämtas, minimeras risken för krångel när dokumentet anropar Google Finance. I min privata version har jag även lagt till ytterligare värden som räknas fram automatiskt, så att jag får fram allt med så få klick som möjligt. I mallen saknas dock vissa värden som bara är relevanta om en strategi har följts i över ett år.
Alla dessa justeringar har gjort att jag kunnat halvera den tid jag lägger på sammanställningar. En månad utan rebalanseringar tar nu mindre än en timma att sammanställa (trots 4 strategier). Det är ju alltid trevligt att få tid över för annat, eller hur?
Med det nya arbetssättet för jag (precis som tidigare) in eventuella utdelningar som varit under månaden. I mallen görs det i samma blad för båda metoderna. Sedan ändrar jag värdet i en cell i dokumentet. Totalavkastningen räknas därefter fram automatiskt för alla mina bolag. I mallen kan ni se att jag slänger med kursutvecklingen för bolagen de senaste 6 månaderna på köpet. Jag kommer till varför längre ned i inlägget.
Jag kopierar sedan över alla värden till ett annat dokument (i Excel) och har därför inget behov av att hela tiden hämta siffror för tidigare månader. Trots det kan jag när som helst skriva in en siffra som motsvarar en tidigare månad, och hämta totalavkastningen för den månaden istället. Alla tidigare utvecklingar går alltså fortfarande att komma åt för den som inte sammanställer allting varje månad. Smutt.
Vidare bearbetning i Excel
För att uppdatera de rapporter som finns på sidan över resultat behöver alla dessa värden läggas över i Excel. Då kan det lätt bli strul med formatet på värdena (t.ex. det faktum att Google Sheets och Excel använder olika tecken för “minus”). Lyckligtvis går även allt detta att komma undan på ett helt automatiserat sätt!
I mitt Excel-dokument har jag gjort en tvåstegsprocess som ändrar formatet till något Excel kan läsa. Bolagen sorteras sedan efter deras totalavkastning. Mina prydliga rapporter uppdateras då automatiskt med nya värden från den sista tabellen, varpå jag endast sparar dem som en PDF och lägger upp på bloggen! För att sortera och ersätta värden i Excel använder jag formlerna i bilden nedan.
En parentes, som jag är väldigt nöjd över, är att kursutvecklingen de senaste 6 månaderna omvandlas till en pil som visar trenden när jag väl lägger in det i Excel. Det är en tjusig finess som jag åstadkommit genom att byta teckensnitt till Wingdings, som använder symboler istället för siffror och bokstäver. Negativa kursutvecklingar omvandlas till det som i Wingdings är en nedåtpekande pil, medan positiva får en uppåtpekande pil. Med villkorsstyrd formatering kan symbolerna sedan ges en röd eller grön färg, beroende på vilken symbol cellen innehåller.
Jag har rödmarkerat de delar som nu uppdateras automatiskt i mina rapporter. Det manuella arbete som återstår är att flytta över månadsutvecklingen och utvecklingen sedan portföljstart till andra tabeller i Excel, men det går på några sekunder.
Jag har uppdaterat mallen i länken nedan, så att den nu innehåller båda arbetssätten. När du har kopierat över den kan du börja fylla på med egna bolag (eller modifiera den efter dina behov). I utgångsläget är den förberedd för 12 bolag och 5 år framåt.
Portföljsammanställning_AV_Mall
Alternativ till Google Finance / Användbara länkar
Google Finance i all ära. Ibland blir det dock för jobbigt att Google Sheets ofta strular när den försöker hämta värden därifrån. Som tur är går det även att hämta finansiella data till Google Sheets från många andra håll.
Handlingen att automatiskt hämta data från olika hemsidor (s.k. “web scraping”) strider i många fall mot sidornas användarvillkor. Det är smått ironiskt eftersom samma sidor alltid tillåter världens största “scraper” (Google) att härja fritt på deras sidor och hämta vad de vill, när de vill. Att då förbjuda privatpersoner att ibland hämta en handfull värden är löjligt. Hur som helst, villkor är villkor. Därför kommer jag inte gå in i detalj på hur du gör detta från exempelvis Avanza eller Bloomberg. Däremot kan jag nämna det uppenbara, nämligen att det är fullt möjligt att automatiskt hämta värden från i princip vilken tabell som helst på en hemsida. Sedan är det upp till dig vad du gör med den informationen.
Här nedanför finns två bra guider för hur du gör detta på Bloomberg och Finviz. Dessa kan vara bra att känna till för att inte vara helt beroende av Google Finance (vars framtid har varit omdiskuterad i många år). Tyvärr har jag inte hittat några bra guider till hur detta kan göras från andra sidor (Yahoo, Nasdaq).
Bloomberg:
Finviz (saknar svenska bolag):
När du kopierar formler från andra sidor på nätet kan det vara bra att känna till att de i USA/Storbritannien använder komma där vi använder semikolon. Formlerna kan därför behöva modifieras lite för att få dem att fungera. Olika hemsidor använder också olika sätt att skriva tickers på (se exemplet för Eolus Vind nedan).
Bloomberg: EOLUB:SS
Google Finance: STO:EOLU-B
Yahoo: EOLU-B:ST
Översätta formelnamn mellan olika språk
Slutligen vill jag tipsa om en sida där jag tar 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.
http://dolf.trieschnigg.nl/excel/index.php?langids=en+se
Mycket nöje!