söndag 14 december 2014

Bevakningslista









Jag har på senare tid försökt att skapa en bevakningslista som uppdaterar sig självt direkt i Excel 2007 på datorn det var inte helt enkelt att hitta och lista ut hur man skulle gå tillväga.

Bara för att underlätta något för någon annan som kanske umgås med liknande ambitioner som jag själv så gör jag en enkel lathund här.

Det första man behöver göra är att öppna Excel och välja "spara som" Döpa sin Bevakningslista välja var den skall sparas och i rullgardinsmenyn välja att spara den i "Makroaktiverat" format (.xlsm) Det går även att använda binär eller Excel 97-2003 format (.xls) Det kanske vanligare formatet (xlsx) bör undvikas.

Lånar delar av en bevakningslista för exemplets skull som kan se ut och utformas någonting i stil med följande.


Bild 1A.

Listan har några förkortningar som kan vara lite svårtolkade för ovana. <EPSLTM> står för Earnings per share last twelve months. <NEPS> för Normalized EPS vilket vill säga bedömd normal vinst per aktie slaget på en 5 årsperiod tex.
<Mot P.valuta> = Motiverade priset bedöms genom att ta en rimlig P/E gånger sin bedömda NEPS direkt i den befintliga valutan för det värdepapper man avser.

<Mot pris SEK> tar man <mot P.valuta> x U$D tex. =SUMMA(H14*$B$4) för Coca Cola ovan.
Cell H14 innehåller summan i den valuta som pappret handlas i emedan B4 innehåller dagens dollarvärde. Har ännu inte lyckats hitta hur man uppdaterar valutorna automatiskt men kanske får något tips efter publicering av detta om jag har tur. Dom sista 3 kolumnerna går vi igenom efter VBA scriptet lagts till.

Nu är det dags för den viktigaste delen som är att skapa ett VBA script kopplat till excel-filen.
För att få fram Microsoft Visual Basic så trycker man in vänstra <Alt> tangenten på tangentbordet och trycker på <F11> medans man håller ner den, så får man fram en VB hanterare.
Genom att i vänstra delen klicka med höger musknapp över blad1 och välja <infoga> <modul>
får man fram en modul där koden jag klistrar in under bilden skall klistras in. Det ser ut såhär:

Bild 2A.

Koden som ska klistras in är följande:


 Function StockQuote(strTicker As String, Optional dtDate As Variant)
  ' Date is optional - if omitted, use today. If value is not a date, throw error.
  If IsMissing(dtDate) Then
  dtDate = Date
  Else
  If Not (IsDate(dtDate)) Then
  StockQuote = CVErr(xlErrNum)
  End If
  End If

 Dim dtPrevDate As Date
  Dim strURL As String, strCSV As String, strRows() As String, strColumns() As String
  Dim dbClose As Double

 dtPrevDate = dtDate - 7
' Compile the request URL with start date and end date
 strURL = "
http://ichart.finance.yahoo.com/table.csv?s=" & strTicker & _
 "&a=" & Month(dtPrevDate) - 1 & _
 "&b=" & Day(dtPrevDate) & _
 "&c=" & Year(dtPrevDate) & _
 "&d=" & Month(dtDate) - 1 & _
 "&e=" & Day(dtDate) & _
 "&f=" & Year(dtDate) & _
 "&g=d&ignore=.csv"

' Debug.Print strURL
Set http = CreateObject("MSXML2.XMLHTTP")
  http.Open "GET", strURL, False
  http.Send
  strCSV = http.responseText

' Debug.Print strCSV
' The most recent information is in row 2, just below the table headings.
 ' The price close is the 5th entry
  strRows() = Split(strCSV, Chr(10)) ' split the CSV into rows
  strColumns = Split(strRows(1), ",") ' split the relevant row into columns. 1 means 2nd row, starting at index 0
  dbClose = strColumns(4) ' 4 means: 5th position, starting at index 0

  ' Debug.Print vbLf
  ' Debug.Print strRows(1)
  ' Debug.Print "dbClose: " & dbClose

 StockQuote = dbClose
 Set http = Nothing
 End Function

Sen sparar man och går tillbaka till excel med vänster <Alt> + <F11> igen.
Känner man sig osäker på VB hanteraren så kan man se en film hur det görs här
https://www.youtube.com/watch?v=KHO5NIcZAc4

Nu för att hämta stängningspriser på det papper det gäller så tar vi Bild 1A. ovan som exempel igen.
Den Cell som är markerad i bilden är Cell K 15 som gäller Lundbergföretagen (LUND-B.ST).
I den cellen klistrar vi in följande kommando =StockQuote("LUND-B.ST") och trycker på enter, då hämtas automatiskt senaste kurs ifrån finance.yahoo.com.

Skulle formatet bli felaktigt för att man har en svensk dator kan man i kontrollpanelen -> Nationella inställningar  på format fliken klicka på ytterligare inställningar och i fliken valuta ändra decimaltecken från ett komma till en punkt för att åtgärda det.

Åter till kolumnerna så i kolumnen <Kurs SEK> uppe i  Bild 1A.  tar vi Coca Cola som exempel igen
och hänvisar cellen J 14 till växlingskursen för U$D i cell B4 med följande formel
  =SUMMA(K14*$B$4)   och slutligen så lägger vi in formeln under <K/M> kolumnen, på raden för Coca Cola, =OMFEL(J14/I14;"")  Sen är det bara sista steget kvar vilket är att markera från L9 och neråt så långt man avser till kanske L30 tex. och klicka med höger musknapp över det markerade området och  välja formatera celler och välja Procent utan decimaler. Med området ännu markerat klickar man på <villkorstyrd formatering> uppe bland menyknapparna och väljer hantera regler, där skapar man en ny regel i taget. första regeln väljer man <formatera endast celler som innehåller> såhär

Bild 3A.
Andra regeln mellan 1, 1,5 med gult och sista mindre än 1 med grönt för att få automatiska färger.
Alla fyra inlagda ser ut såhär.

Hoppas det hjälper någon annan att göra det lite snabbare om någon har intresse för det.
En länk till mera info om man vill ha ytterligare funktioner.
http://www.financial-modelling.net/tutorials/excel/open-excel-retrieve-stock-quotes-in-a-formula

Tillägg:
Hittade ett bra sätt att enkelt åtgärda valutauppdatering. Skapa ett nytt blad i en ny flik i arbetsboken
som man kanske döper till Xrates tex. markera cell A1. Där väljer man Data menyn och hämtar externa data med en webbfråga och klistrar in följande adress-> http://moneycentral.msn.com/investor/external/excel/rates.asp och trycker på knappen <Gå Till>.
Markera den gula pilen i övre vänstra hörnet så den blir grön och tryck på importera nere i högra hörnet. Sen är det bara att koppla cellerna i huvudfliken (B4->B7)till sina respektive motsvarigheter på Xrates sidan med hjälp av sigmaknappen (Σ).

Finns ett par saker att hålla i minnet, uppdaterar gör man genom att spara och alla aktiepriser som inhämtas är senaste stängningskurs. Det går att ändra i VBA scriptet för mer realtid men för värdeinvesterare har det liten betydelse eftersom vi oftast använder listan för att se när aktier börjar komma i "Range" för sitt intrinsic value.

För sorteringens skull kan man markera hela området och uppe i menyn välja < formatera som tabell> samt kryssa i att man har överskrifter. sen kan man enkelt sortera från minsta till största i högra kolumnöverskriften. Labba i en kopia av filen innan permanentande ifall det blir fel och var noga med att sätta referenser till valuta med $ framför koordinaterna, B3-B7 i exemplet bör skrivas $B$3 - $B$7 innan man sorterar annars finns en risk att även dom ändrar sig dynamiskt.

VBA-kurs på utube https://www.youtube.com/watch?v=KHO5NIcZAc4

21 kommentarer:

  1. Copy paste Lundaluppen på riktkurser

    SvaraRadera
    Svar
    1. Det stämmer Martin! Som jag skrev i texten "Lånar delar av en bevakningslista för exemplets skull som kan se ut och utformas någonting i stil med följande."

      Det är för illustrationens skull för att kunna visa exempel i lathunden på hur man kan göra med datat om någon haft ide´n att göra en lista med automatisk uppdatering själva i Excel, det var halvmarigt att få det att fungera så förhoppningen är att lathunden hjälper andra att spara tid om dom vill göra samma sak. Det ska enligt uppgift vara lite enklare i Google docs men många vill säkert göra det i egen Excel på hemdatorn. För att kunna visa färgregler mm behövde jag några i varje kategori så det är inte avsett att vara förslag på bolag eller någon avsikt att påstå att det är riktkurser jag själv räknat fram eller liknande utan bara inlånat för smidighetens skull för att kunna visa exempel på hur jag fick riktkurserna att uppdateras över nätet.

      Radera
  2. Det verkar väldigt krångligt jämfört med att använda Google Docs. Vad är fördelen med att ha det i Excel?

    SvaraRadera
    Svar
    1. Du har säkert rätt, jag har inte erfarenhet av Google Doc´s first hand men har som sagt hört hintar om det. Fördelen med Excel är nog närmast att många kanske har sina uträkningar i Excel redan från början och kanske kopplar uppgifter emellan dokument och databaser redan i datorn eller så kanske det bara är en smakfråga, själv använder jag mina översikts sidor som genereras från andra flikar där jag har både Resultat, Balans och kassaflödes analyser både på mångårsbasis och kvartalsbasis som leder ut i översiktsbladet som jag emellanåt använder som underlags sidor i aktieträffar och dylika sammanhang. Jag har översiktssidor som jag använder både för deklaration och beslutsunderlag som samlar uppgifter ur övriga filer så jag fortsätter bara att bygga på mitt egna "ecosystem" så att säga.:-)

      Radera
  3. Jag får inte med någon punkt det står bara för Lundbergs tex. 32330 istället för 323,30. Vad kan det bero på?

    SvaraRadera
    Svar
    1. Förutsatt att du kollat det jag skrev ovan? -

      "Skulle formatet bli felaktigt för att man har en svensk dator kan man i kontrollpanelen -> Nationella inställningar på format fliken klicka på ytterligare inställningar och i fliken valuta ändra decimaltecken från ett komma till en punkt för att åtgärda det."

      Och checkat att cellen är formaterad som den ska, Höger musknapp valuta ; kr; 2 decimaleroch det inte hjälper ska det finnas ett VBA kommando som heter { dbClose = Val(strCSV) } exakt hur man implementerar det låter jag vara osagt men en fullständig kurs finns här-> https://www.youtube.com/watch?v=KHO5NIcZAc4 Med vänlig hälsning.

      Radera
    2. Grymt! Tack! Nu fungerar det typ förutom att de inte ligger i lika fin ordning som du har.

      Radera
    3. Markera hela området och lägg på en formatering som tabell och kryssa i att du har över skrifter så kan du sortera i högra kolumnen. Men börja labba i en kopia av filen så du inte förstör hela jobbet om nåt blir fel. :-)

      Radera
  4. Tack för tips och beskrivning. :)

    SvaraRadera
  5. Så lite, verkar dock som MSN ändrat på länken till valutor tyvärr.
    Med vänlig hälsning.

    SvaraRadera
  6. Alternativt så byter du ut punkten mot komma i VBA skriptet när texten finns i variabeln dbClose.
    Ex: dbClose = Replace(strColumns(4), ".", ",")
    /John

    SvaraRadera
    Svar
    1. Tack för bidraget John, vet någon en ny plats för Automatisk Valuta eller alternativt hur man implementerar nedtagning av ex. USDSEK=X i detta script så tas förslag tacksamt emot.

      Radera
  7. Hej!
    Hur får man ut för Normalized EPS?
    Tex Treårig medelvärde på EPS 2014,2015,2016 ?
    Vart kan man hämta EPS data för tex 2014 och 2015 ?
    Tack på förhand!
    /Peter

    SvaraRadera
    Svar
    1. I årsredovisning på bolagets hemsida, eller på Google finance eller borsdata.se om man tecknar sig. Mvh.

      Radera
    2. I årsredovisning på bolagets hemsida, eller på Google finance eller borsdata.se om man tecknar sig. Mvh.

      Radera
    3. I årsredovisning på bolagets hemsida, eller på Google finance eller borsdata.se om man tecknar sig. Mvh.

      Radera
    4. Den här kommentaren har tagits bort av skribenten.

      Radera
  8. Kanon! Tack! Hittade EPS för 2015,2014,2013 i HM årsredovisning. Hittar inte på Google finance, du har ingen länk dit för att hitta EPS för 2015,2014,2013 ? Smidigare om det går att hitta där =)

    Tack!
    Mvh

    SvaraRadera
  9. Enklast är nog i femårsöversikten i årsredovisningen vilket de flesta svenska bolag tillhandahåller, för utländska bolag så 4traders eller gurufokus brukar duga. Mvh

    SvaraRadera
  10. Enklast är nog i femårsöversikten i årsredovisningen vilket de flesta svenska bolag tillhandahåller, för utländska bolag så 4traders eller gurufokus brukar duga. Mvh

    SvaraRadera