DIY sijoitustyökalut VBA:n avulla | Stoalainen sijoittaja

Verkossa on tarjolla erinäisiä työkaluja sijoittamisen tueksi. Osa työkaluista on ilmaisuudestaan huolimatta hyviä. Puhelimelta löytyy pari sovellusta, jota käytän aktiivisesti elämän overhead slottien aikana, kuten bussia tai ihmisiä odotellessa. iPhonen mukana tuleva osakesovellus on kelpo tapa pysyä reaaliaikaisesti kärryillä oman seurantalistan osakkeista sekä indeksien kehityksestä. Myös Bloombergin puhelinsovelluksen kautta pysyy kärryillä markkinoiden tunnelmista.

En kuitenkaan ole löytänyt hyvää ja ilmasta ohjelmaa, jolla omaa osakesalkkua voisi hallita. Harrastesijoittajalla ei ole varaa maksaa kalliita Bloomberg tai Factset -lisenssiä, joilla pääsi tiedon äärelle ja saisi tarvittavat luvut erinäisillä plugeilla näppärästi Exceliin. On vaan otettava härkää sarvista ja tehtävät omat sijoitustyökalut. Ajatuksena on automatisoida sijoitustyökalun toimintoja niin paljon kuin mahdollista. Onneksi Excelissä löytyy sisäänrakennettuna oma ohjelmointikieli VBA (Visual Basic for Application), joilla automatisoinnin pystyy helposti itse koodaamaan. Sijoittamisessa VBA:n käyttökohteita ovat tekninen analyysi, portfolion hallinta sekä osakevalinta (screener). Näihin teemoihin pyrin koodaamaan VBA:lla oman Excelpohjaisen työkalun

Ensimmäinen askeleeni VBA:n maailmaan oli teknisen analyysin työkalu. Netistä löytyy hyviä vastaavia työkaluja esimerkiksi http://stockcharts.com/. Keskeinen ongelma näissä ohjelmissa on se, että osakemaailma on rajoittunut Yhdysvalloissa listattuihin yhtiöihin. Tämä on erityisen suuri rasite minulle, sillä etsin osakkeita globaalisti. Lisäksi ohjelman käyttö omassa Excelissä on mielestäni tehokkaampaa, sillä tickerin pystyy vaihtamaan näppärästi soluviittausten avulla.

Suuri hyöty automatisoinnissa tulee mielestäni oman salkun päivityksessä. Aikaisemmin päivittelin kurssitietoja manuaalisesti, mikä johti lopulta siihen, että en jaksanut ylläpitää salkkutietoja ollenkaan. Nyt olen tammikuun aikana koodaillut uutta salkkutyökalua, jolla oman portfolion kurssitietojen päivitys pitäisi jatkossa onnistua nappia painamalla. Tulostuksena on salkun jakautuminen muiden muassa tyylin, toimialan sekä valuutan suhteen. Tähän työkaluun ajattelin palata myöhemmin.

Seuraava askeleeni VBA:n hyödyntämisessä on portfolion tilastollinen analysointi. Tällä viittaan Harry Markowitzin Modern Portfolio Theoryyn. Olen kokenut rahoituksen portfoliomallien olevan hyvin teoreettisia johtuen aiheen matemaattisuudesta. Kiinnostuin kuitenkin tilastollisista menetelmistä uudestaan, sillä nyt käsillä on omat osakkeet ja salkku sekä VBA:n avulla pystyy tekemään analyysin oikeilla kurssitiedoilla. Vaikka kurssien historiatiedot eivät ennustaisikaan tulevaan, uskon silti niillä olevan käyttöarvoa esimerkiksi osakkeiden välisen korrelaation kuvaamisessa.

Kolmas VBAn sovelluskohde sijoittamisessa on oman osakescreenerin rakentaminen. Erityisesti momentumiin perustava screenerin rakentaminen vaikuttaa mielenkiintoiselta. Tällä hetkellä minulla ei ole puutetta osakeideoista, minkä vuoksi en ole pitänyt kiirettä tämän hankkeen suhteen.

Osakekurssit Exceliin

investexcel.net oli ainoa hyvä sivusto, jonka löysin excelpohjaisen sijoittamisen tiimoilta. Tästä syystä ajattelin kontribuoida aiheeseen suomalaisen sijoittajan näkökulmasta jakamalla omia kokemuksia. VBA:n perustiedot ottaa haltuun esimerkiksi googlaamalla ”VBA” ja ”perusteet”. Keskityn alla VBA:n hyödyntämiseen sijoittamisessa ja erityisesti osaketietojen hankkimiseen.

Ensimmäinen huomioitava seikka on se, että osaketiedot hakeva scripti ei tue suomalaisia maa-asetuksia päivämäärän sekä lukujen suhteen. En esimerkiksi saanut suoraan omaa teknisen analyysin työkalua toimimaan mutsin koneella, sillä maa-asetukset olivat siinä väärässä asennossa.

Osakehakuscripti vaatii, että aika on esitettävä muodossa dd/MM/yyy muodossa. Suomessahan on normaalisti 31.1.2016. Itse en tätä pystynyt kiertämään muutoin kuin muuttamalla koneen maa-asetuksia. Scripti hakee luvut jenkkityyliin siten, että desimaalit erotetaan pisteellä. Jotta oma suomenkielinen excelinä osaisi tulkita osaketietoja oikein, täytyy pisteet korvata pilkuilla.

Aluksi käytin Google Financea pohjautuvaa osakekurssien hakuscriptiä. Tämä oli virhe, sillä Googlen kautta ei ainakaan silloin saanut eurooppalaisten yhtiöiden osaketietoja. Olin jo luovuttamassa koko projektia, sillä tarvitsen kaikkien maiden osaketietoja. Suureksi onnekseni Yahoo tarjoaa kaikkien itselle relevanttien maiden kurssitiedot. Käsittääkseni iPhonen osakesovellus hakee samasta paikasta tiedot, joten toivotaan palvelun säilyvän. VBA-koodissa pitää käyttää Yahoon tickereitä, jotka saa helposti haettua vaikka finance.yahoo.com kautta. Esimerkiksi Ahlstromin tickeri Yahoossa on AHL1V.HE.

Ohessa kyheilemäni VBA-koodi, jolla osaketiedot saa Analysis-sheetille suomenkielisen Excelin ymmärtämään muotoon. GetData proseduuri hakee osto- ja myyntipäivien sekä tickerin perusteella. Osakekurssit Data-sheetille. TransferData proseduuri muuntaa osakekurssit analysoitavaan numeromuotoon Analysis-sheetille. Näiden kahden proseduurin ympärille voi rakentaa varsinaisen ohjelman.

Sub MainProgram()

‘*** CREATE DATA & ANALYSIS SHEETS

Dim Data As Worksheet

Dim Analysis As Worksheet

Set Data = Sheets.Add(After:=Sheets(Worksheets.Count))

Data.Name = “Data”

Set Analysis = Sheets.Add(After:=Sheets(Worksheets.Count))

Analysis.Name = “Analysis”

‘*** GET NOKIA’S STOCK DATA ***

Call GetData(“25/01/2016”, “29/01/2016”, “NOK”)

Call TransferData

End Sub

‘*** GET STOCK DATA ***

Sub GetData(startDate As Date, endDate As Date, Ticker As String)

Dim qurl As String

Dim nQuery As Name

Dim lastRow As Integer

Dim Msg As String

Dim xName As Name

Dim xTable As QueryTable

Sheets(“Data”).Cells.Clear

Sheets(“Data”).Select

Sheets(“Data”).Range(“a1”).CurrentRegion.ClearContents

qurl = “http://ichart.finance.yahoo.com/table.csv?s=” & Ticker

qurl = qurl & “&a=” & Month(startDate) – 1 & “&b=” & Day(startDate) & _

“&c=” & Year(startDate) & “&d=” & Month(endDate) – 1 & “&e=” & _

Day(endDate) & “&f=” & Year(endDate) & “&g=” & Sheets(“Data”).Range(“a1”) & “&q=q&y=0&z=” & _

Ticker & “&x=.csv”

QueryQuote:

With Sheets(“Data”).QueryTables.Add(Connection:=”URL;” & qurl, Destination:=Sheets(“Data”).Range(“a1”))

.BackgroundQuery = False

.TablesOnlyFromHTML = False

.Refresh BackgroundQuery:=False

.SaveData = True

End With

Sheets(“Data”).Range(“a1”).CurrentRegion.TextToColumns Destination:=Sheets(“Data”).Range(“a1”), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

Semicolon:=False, Comma:=True, Space:=False, other:=False

Sheets(“Data”).Columns(“A:G”).ColumnWidth = 12

lastRow = Sheets(“Data”).UsedRange.Row – 2 + Sheets(“Data”).UsedRange.Rows.Count

If lastRow > 2 Then

Sheets(“Data”).Sort.SortFields.Add Key:=Range(“A2:A” & lastRow), _

SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

End If

For Each xTable In Application.ActiveSheet.QueryTables

xTable.Delete

For Each xName In Application.ActiveSheet.Names

xName.Delete

RemoveConnections

End Sub

‘*** Korvaa pisteet pilkuilla

Sub TransferData()

Dim lastColumn As Integer

Sheets(“Analysis”).Cells.Clear

Sheets(“Analysis”).Select

‘Kirjoita otsikot

ActiveSheet.Range(“C3”).Value = “Date”

ActiveSheet.Range(“C4”).Value = “Stock price”

ActiveSheet.Columns(“C:C”).EntireColumn.AutoFit

‘Kopio osakekurssit

If IsEmpty(Sheets(“Data”).Range(“E3”)) = True Then

Sheets(“Data”).Select

ActiveSheet.Range(“E2”).Select

Selection.Copy

Sheets(“Analysis”).Select

ActiveSheet.Range(“D4”).Select

ActiveSheet.Paste

‘Muuta numeroformaattiin

ActiveSheet.Range(“D7”).Select

ActiveCell.FormulaR1C1 = “=NUMBERVALUE(R[-3]C,””.””)”

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Selection.NumberFormat = “0.00”

Selection.Cut

ActiveSheet.Range(“D4”).Select

ActiveSheet.Paste

‘Kopio päivämäärät

Sheets(“Data”).Select

ActiveSheet.Range(“a2”).Select

Selection.Copy

Sheets(“Analysis”).Select

ActiveSheet.Range(“D3”).Select

ActiveSheet.Paste

Selection.NumberFormat = “dd/mm/yy;@”

Else

Sheets(“Data”).Select

ActiveSheet.Range(“e2”, ActiveSheet.Range(“E2”).End(xlDown)).Select

Selection.Copy

Sheets(“Analysis”).Select

ActiveSheet.Range(“D4”).Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=True

‘Muuta osakekurssit numeroformaattiin

ActiveSheet.Range(“D7”).Select

ActiveCell.FormulaR1C1 = “=NUMBERVALUE(R[-3]C,””.””)”

Selection.Copy

Worksheets(“Analysis”).UsedRange

lastColumn = ActiveSheet.Range(“D4”).SpecialCells(xlCellTypeLastCell).Column

ActiveSheet.Range(ActiveSheet.Cells(7, 4), ActiveSheet.Cells(7, lastColumn)).Select

ActiveSheet.Paste

Selection.NumberFormat = “0.00”

Selection.Copy

ActiveSheet.Range(“D4”).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveSheet.Range(ActiveSheet.Cells(7, 4), ActiveSheet.Cells(7, lastColumn)).Select

Selection.ClearContents

‘Kopio päivämäärät

Sheets(“Data”).Select

ActiveSheet.Range(“a2”, ActiveSheet.Range(“a2”).End(xlDown)).Select

Selection.Copy

Sheets(“Analysis”).Select

ActiveSheet.Range(“D3”).Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=True

Selection.NumberFormat = “dd/mm/yy;@”

End If

End Sub

Sub RemoveConnections()

Dim i As Long

For i = ActiveWorkbook.Connections.Count To 1 Step -1

ActiveWorkbook.Connections.Item(i).Delete

Next i

End Sub

Lopuksi testausvaiheessa Excel jäätyi ohjelman puolessa välissä, kun päivitin kaikkien salkkuyhtiöiden osakekursseja. Ilmeisesti selaimen välimuisti täyttyy noin 10 osakehaun jälkeen (GetData proseduuria kutsuttu For loopissa). Ongelma ratkesi, kun IE:n välimuistin tyhjää kesken for loopin.

Shell “RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 255”

Osakekurssit Exceliin

VASTAA Peruuta