Tämä postaus on ollut jo vuosia blogin drafteissa, mutta en ole vain saanut aikaiseksi viimeistellä tekstiä. Nyt kuitenkin olen saanut viimeaikoina useammankin yhteydenoton toivoen vinkkejä taulukon tekemiseen, joten ajattelin jakaa tätä tietoutta eteenpäin.
Automatisointi on oikeasti melko helppoa eikä vaadi mitään koodarin taitoja, koska Googlen palvelu on tehty niin käyttäjäystävälliseksi. Suosittelen kaikille, jos ette vielä käytä!
Osaketaulukko
Itselläni kaiken datan perusteena on taulukko, jossa on osakkeet, niiden kurssit, hankintahinnat yms. listattuna. Vaihtoehtoja on niin paljon kuin artistejakin, mutta tämä on minun tapani.
- Nykykurssi haetaan kirjoittamalla soluun funktio =googlefinance("TIKKERI"). Esimerkiksi Wal-Martin kurssitiedot haetaan funktiolla =googlefinance("wmt")
- Päivän muutos haetaan funktiolla =googlefinance("TIKKERI";"changepct")/100. Esimerkkinä Wal-Martin päivän muutos haetaan funktiolla=GOOGLEFINANCE("wmt";"changepct")/100
Kaikki muu minun osaketaulukossa on laskentaa, joka perustuu omaan dataan, kuten osakkeiden kappalemäärään ja niiden keskihintaan sekä niistä laskettuun arvoon tai vaihtoehtoisesti nykykurssista laskettuun sijoituksen arvoon.
Koska värikoodaus on mielestäni erittäin visuaalista, niin olen värikoodannut muutos päivässä %, voitto/tappio ja voitto/tappio % solut. Muutos päivässä % solun olen värikoodannut siten, että jos päivän kehitys on ollut positiivinen, eli yli 0%, niin teksti on vihreä ja jos negatiivinen, niin teksti on punainen. Sama juttu voitto/tappio soluille, mutta teksti on musta ja taustaväri on punainen / vihreä riippuen onko sijoitus voitolla vai tappiolla. Tämä tehdään maalaamalla solut joihin ehdollinen muotoilu halutaan ja painamalla hiiren oikeaa ja valitsemalla Ehdollinen muotoilu. Siitä aukeaa ikkuna, jossa voidaan lisätä sääntöjä painamalla Lisää uusi sääntö. Sitten vaan valitsemaan minkälaista muotoilua haluaa millekin arvoille.
Koska seuraan salkkuni euromääräistä arvoa ja osa sijoituksistani on muissa valuutoissa, täytyy myös reaaliaikaiset valuuttakurssit hakea taulukkoon. Ne haetaan funktiolla =googlefinance("currency:VALUUTTA1VALUUTTA2") Eli esimerkiksi jos haluan euron kurssin suhteessa dollariin, käytän funktiota =GoogleFinance("CURRENCY:EURUSD"). Tämän kurssin avulla voin muuntaa muissa valuutoissa olevat euromääräiseksi taas ihan perus excelistä tutuilla laskentafunktioilla.
Kaikkia tietoja ei valitettavasti saa googlefinance funktiolla, esim. Seligsonin tietojen tuomiseen käytän import html funktiota. Vien Seligsonin datan omalle laskentavälilehdelle tällä funktiolla =ImportHtml("http://www.seligson.fi/suomi/rahastot/FundValues_FI.html";"table";0) josta sitten haen normaaleilla excelin laskentakaavoilla taas tiedon ylläolevaan taulukkoon. ImportHtml funktio siis hakee taulukon, jossa se nettisivuilla sijaitsee. Tuo ; jälkeen tuleva 0 kertoo taulukon numeron. Tietyillä sivuilla se voi olla joku muu. Koska en ole mikään html koodin asiantuntija, niin itse olen vain yrityksen ja erehdyksen kautta saanut oikean taulukon näkyviin :). Lopputulos näyttää taulukossa tältä.
Salkun kehityksen automaattinen seuranta ja kaavion piirtäminen
Olen automatisoinut salkkuni seurantaa siten, että taulukko tallentaa salkun arvon omalle rivilleen joka arkipäivä. En siis halua, että viikonlopun päivät tulevat taulukkoon mukaan, joten taulukko tuo salkun arvot vain ma, ti, ke, to ja pe aamuyöllä klo 00-01 välisenä aikana.
 |
| Tältä näyttää sitten taulukko lopullisessa muodossaan. |
Tämä ei ole enää ihan perus excelin pyörittelyä, vaan vaatii vähän korkealentoisempaa osaamista., eli pientä skriptin kirjoitustaitoa. Sitä osaamista minulla ei ole, mutta olen tämän koodin häikäilemättä plagioinut joltain toiselta sivustolta pienen googlettelun jälkeen. Plagioikaa siis vapaasti eteenpäin :).
Aloitetaan kuitenkin perusasioilla. Kun olet luonut oman välilehden tälle sijoitusten seurannalle, niin maalaa sarake A ja vaihda solun tyypiksi päivämäärä, koska ao. skripti tuo päivämäärän sarakkeeseen A. Samaten maalaa sarake B ja laita soluun euromääräinen valuutta, koska siihen soluun skripti kirjoittaa salkun euromääräisen arvosi.
Koodia pääsee kirjoittamaan valitsemalla Työkalut valikosta Ohjelman muokkaustyökalun. Tämä avaa uuden ikkunan, johon skriptin voi syöttää. Minun taulukon skripti näyttää tältä:
function addResult() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var portfolioSheet = spreadsheet.getSheetByName("Sijoitukset");
var historySheet = spreadsheet.getSheetByName("Sijoitusten kehitys");
// Fetch date and portfolio value
var value = portfolioSheet.getRange(3, 14, 1, 1).getValue();
var date = new Date();
// Calculate where to write new information
var lastRow = historySheet.getLastRow();
var dateCell = historySheet.getRange(lastRow+1, 1);
var valueCell = historySheet.getRange(lastRow+1, 2);
// Store values
dateCell.setValue(date);
valueCell.setValue(value);
}
Tuohon skriptiin ei sen enempää kannata kiinnittää huomiota, vaan tärkeintä on tietää mitä sinun pitää muokata, että saat oman salkkusi seurannan toimimaan. Salkun seuranta on minulla omalla välilehdellä nimeltään Sijoitusten kehitys. Tuo alussa tehty osakeseurantataulukko on taas nimeltään Sijoitukset.
- PortfolioSheet riville pitää kirjoittaa lainausmerkkien sisään sen taulukon nimi, mistä haetaan koko salkun arvo
- HistorySheet riville pitää kirjoittaa lainausmerkkien sisään sen taulukon nimi, mihin salkun arvo tuodaan joka päivä, eli siihen taulukkoon, mihin haluat sen täyttävän.
- Var value riville sulkujen sisään pitää kirjoittaa se solu mistä tietoa halutaan haettavan. Tämä ei toimi perinteisellä C14 solulla, vaan ensimmäiseen arvoon (minulla 3) kirjoitetaan mones rivi se on ylhäältä katsoen ja toiseen arvoon (minulla 14) kirjoitetaan mones solu se on vasemmalta lukien. Viimeiset 2 numeroa (minulla 1,1) en muista mitä niillä osoitetaan, mutta jos solussa ei ole mitään muuta kuin salkun arvo, niin 1,1 toimii.
Sitten kun tämä on tehty, pitää vielä määritellä milloin skripti kirjoittaa salkun arvon uudelle riville. Tämä tapahtuu painamalla kellon kuvaa työkaluriviltä. Tästä aukeaa ikkuna, johon sitten pitää syöttää ne ajat milloin skripti kirjoittaa salkun arvon. Kuten aiemmin mainitsin, niin minulla skripti kirjoittaa arvon joka arkipäivä klo 00-01. Minulla on siis joka päivälle oma aikataulu. Tämän jälkeen skripti on valmis. Skriptin toimimista voit testata painamalla työkalurivillä kellon oikealla puolella olevaa play näppäintä. Jos ja kun kaikki toimii, play näppäimen pitäisi kirjoittaa sen hetkinen salkun arvo ja päivämäärä omalle rivilleen.
Kuvaajan piirtäminen ja julkaisu omassa blogissa
Koska tykkään käppyröistä, niin tietysti datasta pitää saada päivittyvä kaavio. Kaavion tekeminen tapahtuu kuin missä tahansa muussakin. Kannattaa siis maalata sarakkeet A ja B ja luoda kaavio, jolloin kaavio ottaa kaikki A ja B sarakkeiden arvot kaavioon. Itse olen tähän sijoitusten seurantaan käyttänyt aikajanakaaviota, koska siinä voi kätevästi valita eri miltä aikaväliltä kuvaajaa tarkastelee.
Kaavion voit julkaista esim. blogissasi painamalla kaavion oikeasta yläkulmasta kolmea pistettä ja Julkaise kaavio. Itse olen tykännyt upottamisesta, jolla kaavio upotetaan blogiin. Kaavion voi julkaista joko kuvana, jolloin lukija näkee vain kuvan tai interaktiivisena, jolloin lukija näkee hiirellä tietyn kohdan tiedot kaaviosta, eli esimerkiksi salkun kehityksessä päivämäärän ja sen päivän arvon missä kohtaa hiiri kuvaajalla on.
Tämä sama julkaisutapa pätee siis kaikkiin kaavioihin ja vain taivas ja oma mielikuvitus on rajana mitä kaikkia kuvaajia haluat julkaista.
Loppusanat
Kuten on varmaan jo käynyt ilmi, minä tykkään kovasti kuvaajien ja taulukoiden kanssa näpertelystä. Ne tuovat itselle motivaation kannalta tärkeää visualisaatiota ja jotenkin tekevät koko projektista paljon konkreettisemman sekä salkun seurannasta paljon pienempitöisen.
Koska itse tein tämän automatisoinnin jo useampi vuosi sitten, niin voi olla että tekstissä on jotain virheitä. Mikäli joku kohta tökkii, niin autan mielelläni! Toivottavasti tästä tekstistä oli jollekin apua. Mielelläni näkisin teidän lukijoiden versiot omasta salkusta :)
