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