Salkkujen seuranta: excel-taulukot

debit · Huhtikuu 15, 2024, 8.43am

Yahoo Financen HTML on näköjään taas elänyt. Ainakin hintatieto näyttää siirtyneen value -attribuutista data-value -attribuuttiin.

Fiksattu yahoofinance-skripti:

function yahoofinance(symbol, mode="price") {
  symbol = symbol.toUpperCase();
  mode = mode.toLowerCase();

  const content = UrlFetchApp.fetch("https://finance.yahoo.com/quote/" + symbol).getContentText();
  const $ = Cheerio.load(content);

  if (mode === "price") return Number($('fin-streamer[data-symbol="' + symbol + '"][data-field="regularMarketPrice"]').attr('data-value')) || 0;
  else if (mode === "name") return $("div#quote-header-info h1").first().text().split("(" + symbol + ")")[0] || "N/A";
  else if (mode === "change") return Number($('fin-streamer[data-symbol="' + symbol + '"][data-field="regularMarketChange"]').attr('data-value')) || 0;
  else if (mode === "changepct") return Number($('fin-streamer[data-symbol="' + symbol + '"][data-field="regularMarketChangePercent"]').attr('data-value')) || 0;

  var summaryArr = $("div#quote-summary").children().children().children().children().toArray().map((element) => { return $(element).text() });

  if (mode === "high") return Number(summaryArr[4].split(" ")[3].replace(/\,/g, "")) || "N/A";
  else if (mode === "low") return Number(summaryArr[4].split("e")[1].split(" ")[0].replace(/\,/g, "")) || "N/A";
  else if (mode === "high52") return Number(summaryArr[5].split(" ")[4].replace(/\,/g, "")) || "N/A";
  else if (mode === "low52") return Number(summaryArr[5].split("ge")[1].split(" ")[0].replace(/\,/g, "")) || "N/A";
  else if (mode === "open") return Number(summaryArr[1].split("n")[1].replace(/\,/g, "")) || "N/A";
  else if (mode === "volume") return Number(summaryArr[6].split("e")[1].replace(/\,/g, "")) || "N/A";
  else if (mode === "volumeavg") return Number(summaryArr[7].split("e")[1].replace(/\,/g, "")) || "N/A";
  else if (mode === "beta") return Number(summaryArr[9].split(")")[1]) || "N/A";
  else if (mode === "pe") return Number(summaryArr[10].split(")")[1].replace(/\,/g, "")) || "N/A";
  else if (mode === "eps") return Number(summaryArr[11].split(")")[1].replace(/\,/g, "")) || "N/A";
  else if (mode === "edate") return summaryArr[12].split("Date")[1] || "N/A";
  else if (mode === "divdate") return summaryArr[14].split("Date")[1] || "N/A";
  else if (mode === "target") return Number(summaryArr[15].split("st")[1].replace(/\,/g, "")) || "N/A";
  else if (mode === "div") return Number(summaryArr[13].split("ld")[1].split(" ")[0].replace(/\,/g, "")) || "N/A";
  else if (mode === "yield") return Number(summaryArr[13].split("(")[1].split("%")[0]) || "N/A";
  else throw new Error("Unknown mode");
}

Edit:

Haen itse tuolta vain “pricen”, joten en tiedä onko prosenteissa tapahtunut jotain muutosta. Jos käytössä on Google Sheets:n päässä “prosenttiformatointi” kannattaa yrittää jakamista 100:lla.

Esim.

=yahoofinance("NDA-FI.HE";"changepct")/100

Meh · Huhtikuu 15, 2024, 9.45am

Oli tosiaan joku häiriö ettei näyttänyt hintoja, mutta nyt tuon skriptin korjauksen jälkeen näyttää. Tosin itsellä näyttää päivämuutos %:t aika hurjalta, joten jotain on vielä pielessä.

Qwerty123 · Huhtikuu 16, 2024, 3.57am

Hinta näyttäisi pelittävän. Käytän itse tuosta vain divdatea ja se ei itselläni enää toimi. Tällaista pukkaa

debit · Huhtikuu 16, 2024, 7.05am

Näköjään muutkin kentät olivat menneet enemmän tai vähemmän uusiksi.

Loppujenkin kenttien osalta päivitetty skripti:

function yahoofinance(symbol, mode="price") {
  symbol = symbol.toUpperCase();
  mode = mode.toLowerCase();

  const content = UrlFetchApp.fetch("https://finance.yahoo.com/quote/" + symbol).getContentText();
  const $ = Cheerio.load(content);

  if (mode === "price") return Number($('fin-streamer[data-symbol="' + symbol + '"][data-field="regularMarketPrice"]').attr('data-value')) || 0;
  else if (mode === "name") return $("section.container h1").first().text().split("(" + symbol + ")")[0] || "N/A";
  else if (mode === "change") return Number($('fin-streamer[data-symbol="' + symbol + '"][data-field="regularMarketChange"]').attr('data-value')) || 0;
  else if (mode === "changepct") return Number($('fin-streamer[data-symbol="' + symbol + '"][data-field="regularMarketChangePercent"]').attr('data-value')) || 0;
  else if (mode === "high") return Number($('fin-streamer[data-symbol="' + symbol + '"][data-field="regularMarketDayRange"]').attr('data-value').split(" - ")[1].replace(/\,/g, "")) || 0;
  else if (mode === "low") return Number($('fin-streamer[data-symbol="' + symbol + '"][data-field="regularMarketDayRange"]').attr('data-value').split(" - ")[0].replace(/\,/g, "")) || 0;
  else if (mode === "high52") return Number($('fin-streamer[data-symbol="' + symbol + '"][data-field="fiftyTwoWeekRange"]').attr('data-value').split(" - ")[1].replace(/\,/g, "")) || 0;
  else if (mode === "low52") return Number($('fin-streamer[data-symbol="' + symbol + '"][data-field="fiftyTwoWeekRange"]').attr('data-value').split(" - ")[0].replace(/\,/g, "")) || 0;
  else if (mode === "open") return Number($('fin-streamer[data-symbol="' + symbol + '"][data-field="regularMarketOpen"]').attr('data-value').replace(/\,/g, "")) || 0;
  else if (mode === "volume") return Number($('fin-streamer[data-symbol="' + symbol + '"][data-field="regularMarketVolume"]').attr('data-value').replace(/\,/g, "")) || 0;
  else if (mode === "volumeavg") return Number($('fin-streamer[data-symbol="' + symbol + '"][data-field="averageVolume"]').attr('data-value').replace(/\,/g, "")) || 0;
  else if (mode === "target") return Number($('fin-streamer[data-symbol="' + symbol + '"][data-field="targetMeanPrice"]').attr('data-value').replace(/\,/g, "")) || 0;
  
  var summaryArr = $("div[data-testid=quote-statistics]").children().children().children().toArray().map((element) => { return $(element).text() });

  if (mode === "beta") return Number(summaryArr[19].replace(/\,/g, "")) || 0;
  else if (mode === "pe") return Number(summaryArr[21].replace(/\,/g, "")) || 0;
  else if (mode === "eps") return Number(summaryArr[23].replace(/\,/g, "")) || 0;
  else if (mode === "edate") return summaryArr[25] || "N/A"; 
  else if (mode === "divdate") return summaryArr[29] || "N/A";
  else if (mode === "div") return Number(summaryArr[27].split(" ")[0].replace(/\,/g, "")) || 0;
  else if (mode === "yield") return Number(summaryArr[27].split("(")[1].split("%")[0].replace(/\,/g, "")) || 0;

  else throw new Error("Unknown mode");
}

Kevyen testin perusteella näyttäisi toimahtelevan:

Edit:

Paha sanoa, itsellä nuo näyttäisi toimivan ok:
test

Tuo skriptihän pitää ottaa käyttöön navigoimalla Google Sheetsissä Extensions > Apps Script (alkuperäinen käyttöönotto-ohje: Salkkujen seuranta: excel-taulukot - #174 käyttäjältä Prophier)

Joskus toki tuntuu että Yahoon päässä jokin tökkii, ja lopputuloksena on erroria. Tällöin yksittäistä kenttää voi yrittää päivittää hakkaamalla yksittäisessä solussa “Delete, Ctrl+Z” tms.

Edit2:

Sinuna tekisin muutoksen (vain kerran) sinne yahoofinance-skriptin puolelle:

...
  else if (mode === "changepct") return Number($('fin-streamer[data-symbol="' + symbol + '"][data-field="regularMarketChangePercent"]').attr('data-value'))/100 || 0;
...

timontti · Huhtikuu 16, 2024, 7.31am

ping @debit

Miten tätä pitäisi korjata, että saisin google sheetsiin:

Kurssin

Volyymin

ja Volyme avg.

?

Kiitos

Meh · Huhtikuu 16, 2024, 8.23am

Varmaan sitten jotain tuollaista. Outoa vaan kun ei ole itse varsinaisesti muuttanut mitään. Mutta siis 100-kertaisia lukuja näyttää edelleen. Ei kai auta kun näpytellä käsin pariin kymmeneen riviin /100

timontti · Huhtikuu 16, 2024, 8.32am

Kävin tuon tekemäsi skriptin syöttämässä. Se kyllä jotain herjasi siellä, mutta kun tallensin niin tuntui heti lähtevän nämä kuvakaappauksissani olevat ja aiemminkin käyttämäni “skriptit” toimimaan. Hmm

Qwerty123 · Huhtikuu 16, 2024, 7.07pm

Iso kiitos, @debit . Toimii kuin unelma.

Merkkitäytteenä pieni eläköönhuuto foorumin avuliaalle yhteisölle, joka mahdollistaa piensijoittajalle näin hienoja asioita. Eläköön!

Meh · Huhtikuu 27, 2024, 5.07pm

Hirveästi viime aikoina Yahoon hintahakujen kanssa alkanut tulemaan tätä virhettä, mutta vain joidenkin lappujen osalta. Tälle ei varmaan ole täällä päässä mitään tehtävissä? Sotkee vaan kokonaisuuden, jos ei yksikään hinta päivity.

jorgos20 · Toukokuu 27, 2024, 6.34pm

Tämmönen newbie kysymys, mutta laitatteko arvo-osuus tililtä saadut osingot noihin tapahtumiin? Jos ne tililtä uudelleen sijoittaa ja laittaa sitten sinne “Cash” ruutuun, jolloin ne tulee ns kahteen kertaan. Jos laitatte nii mihin kohtaan merkkaatte verot, esimerkiksi “fees” ruutuun?

Qwerty123 · Toukokuu 31, 2024, 11.30pm

Jos puhut sijoitusten seurannasta, niin itse toimin näin. Merkitsen ylös saadut netto-osingot erikseen. Verot ensin pois siis, joten summa on sama kuin tilille tuleva rahamäärä. Olen merkinnyt osingot näin jo vuosia eli ihan alusta asti. Nämä ovat siis kaikki osinkotapahtumat ja oma seurantansa, ettei tulisi tosiaan merkittyä kahdesti sekä osinkona, että käteisenä tilillä. Toki minulla on omilla riveillään näkyvissä myös osakekohtainen osinko ja brutto-osinko, koska tykkään tilastoida ja seurata pikku yksityiskohtia. Itse osinkoraha viipyy sitten hetken taulukossa käteisenä, mutta katoaa sitten käteisosuudesta kun muuttuu uudelleen sijoittaessa osakkeiksi. Salkun kokonaisarvo kunakin hetkenä on siis arvopapereiden arvo ja käteisen arvo. Osinkoseuranta on lopulta pelkkää informaatiota, josta on kiva seurata mistä osa tuotoista on peräisin.

On varmaan aika henkilökohtainenkin asia, että mitä haluaa laskea, kirjata ylös ja millä yksityiskohtaisuudella. Joku ei seuraa ollenkaan ja itse seuraan ja kirjaan ylös tilanteen joka päivä ja päivittäisiä salkkuarvoja onkin exceliin kertynyt jo tuhansia.

Toivottavasti vastasin kysyttyyn.

Qwerty123 · Kesäkuu 4, 2024, 6.18am

Palasin tähän kysymykseen ja heräsin pohtimaan, että kyllä vaan tuosta kaavasta saa vuodet n ratkaistua.

Teoriat ensin

vihreän kuvan kaava
kaavan selitykset
vuodet n ratkaistuna
viiden muuttujan aikafunktio nopeaa käyttöä varten

Tämän funktion voi syöttää sitten vaikka näppärään ilmaiseen laskimeen SpeedCrunch

Tämä antaa kyllä tuon networthify:n linkin arvot, kun tekee samat olettamat kuin sillä sivulla

Ja samat arvot aikafunktiolla

oletukset ovat
-loppupääoma FV on 500000 (eli 20000/0,04, jotta nosto 4 % tuottaa joka vuosi vuosikulut 20000
-alkupääoma P on 0
-vuotuinen korko i on 5 % eli se syötetään kaavaan desimaalilukuna 0,05
-korkoa korolle taajuus c on vähän hassusti 1 eli korot tulevat maksuun kerran vuodessa
-kuukausisumma R on taasen hassusti 30000 eli koko vuoden säästöt, koska korotkin maksellaan vain kerran

Vähän realistisemmilla parametreilla saadaan ajaksi

Tässä erona maksut 12 kertaa vuodessa, jolloin korkoa korollekin alkaa juosta joka kuukausi ja kuukausisumma on sitten vuosisäästön 12-osa. Erona reaalimaailmaan tasaisen tappava kasvu hela tiden.

No jaa, ehkä sen excelissäkin voisi tehdä

Kaava ruudussa G3 on vähän haastava, niin tässä se kopioitavaksi
=LUONNLOG((D3 * E3 + C3 * A3) / (D3 * E3 + C3 * B3))/(D3 * LUONNLOG((D3 + C3) / D3))
(toimii siis suomenkielisessä excelissä sellaisenaan)

Have fun!

Qwerty123 · Kesäkuu 4, 2024, 8.55am

jatkoa…

Tässä erilaisia näppäriä laskutapoja kätevästi yhdessä excelissä.

Pinkit täytellään ja vihreät laskee excel.

ja sitten reunat näkyville, että löytää oikeat solut kaavoihin alle

Vihreiden ruutujen kaavat ovat:
I6
=C6 * (1 + D6 / E6)^(F6 * E6) + G6 * ((1 + D6 / E6)^(F6 * E6) - 1)/(D6 / E6)

I11
=LUONNLOG((F11 * G11 + E11 * C11) / (F11 * G11 + E11 * D11)) / (F11 * LUONNLOG((F11 + E11) / F11))

E16
=C16 / D16

K16
=LUONNLOG((H16 * I16 + G16 * E16) / (H16 * I16 + G16 * F16)) / (H16 * LUONNLOG((H16 + G16) / H16))

Verdin · Kesäkuu 15, 2024, 5.25pm

Jos vielä löytyy pohja Apple OS Numbers maailmaan johon saa yksinkertaisen portfolion niin kiinnostaisi kuulla. Pahoittelu että kommentti sotkettu Excel keskustelun joukkoon.

Vanhalla · Kesäkuu 18, 2024, 11.57am

Jos ei ole pakko olla nimenomaan Numbers pohja vaan softakin kelpaa niin testaa ihmeessä: https://stockle.app, kahden devaajan voimin väsätty alusta ja ilmaisversiokin löytyy jollei omista kuin muutamia positioita.