Calc ulkoinen tietolähde

[AimoE]

Olen käytellyt LibreOffice Calcia satunnaisesti joihinkin tehtäviin, mutta en ole paneutunut sen koko ilmaisuvoimaan. Nyt olen tekemässä asiakirjaa, johon haluaisin poimia tietoja ulkoisesta lähteestä. Hakusanoilla "ulkoinen lähde" tai "external source" löydän ohjeen siitä miten tiedot linkitetään, mutta en keksi hakusanoja joilla löytäisin tietoa siitä millä tavalla ulkoinen lähde kannattaa muotoilla tätä tarkoitusta varten.

Haluaisin kirjoittaa tieodt tekstitiedostoon, mutta mikä formaatti sillä pitää olla? Teen asiakirjaa jossa poimittuja tietoja tarvitaan sinne tänne ripoteltuina. Tiedot eivät siis ole olemukseltaa taulukkomaisia - tai osa on, mutta hyvin pieni osa. Loput ovat yksittäisiä tietoja ja tietoryhmiä, jotka haluan asetella asiakirjaan selitteineen, ja solunimen (melkein) jokaiselle tiedolle erikseen. Mistä löydän tietoa siitä missä muodossa lähdetiedosto kannattaa suunnitella tällaiseen tarkoitukseen?

[mpiso]

Jos tarkoitat web-kyselyä, niin ainakin minulla (versio 5.1.6.2) ohjeessa lukee ”Lisätään käsiteltävään taulukkoon linkkinä tietoa HTML-, Calc- tai Excel-tiedostosta. Aineiston pitää olla nimetyllä alueella”. Tuohan on tarkoitettu taulukon noutamiseen lähinnä HTML tiedostosta, joka voi olla myös omalla koneella. Valikossa se kuitenkin on ”Kytke ulkoiseen tietolähteeseen”.

Sitten on ”Taulukon lisääminen tiedostosta”. Silloin tiedosto on yksinkertaisimmillaan puhdasta tekstiä, jollain välimerkillä erotettuna. Tuokaan ei tunnu oikealta koska sillä muodostetaan taulukko. Aloitusrivin voi valita ja sarakkeet piilottaa (poistaa), mutta tiedosto luetaan loppuun asti.

Siten on tietysti tietokanta, muta sekään ei oikein sovi tuohon mitä kerroit.

Auttaisi paljon jos kertoisit miten liität ulkoisen lähteen.

[AimoE]

Olen tekemässä asiakirjamallia, johon kuuluu kolmenlaisia Calc-välilehtiä. Calc-suomennos nimittää välilehtiä taulukoiksi, mutta tässä tapauksessa nimitän niitä mieluummin lomakkeiksi. Asiakirjassa on yksi lomake, jonka tiedot syötän kerran vuodessa ja muutaman lomakkeen joukko, joihin syötän tietoja kuukausittain, sekä yksi lomake, joka sisältää pelkkiä laskentakaavoja. Kaavoja on kaikilla lomakkeilla, mutta koko homman päätavoite on se laskenta, jonka tämä viimeksi mainittu lomake suorittaa. Koko asiakirja on muotoiltu niin, että sen voi tarvittaessa tulostaa paperille.

Kysymykseni koskee sitä lomaketta, jonka tiedot syötän kerran vuodessa.

Haluan voida muokata asiakirjamallin ulkoasua ja sitten tehdä siitä vuosikohtaisia kopioita ilman, että mun tarvii niitä vuosikohtaisia vakiotietoja naputtaa uudelleen joka kopioon vain siksi, että asiakirjan muotoilu on muuttunut. Haluan siis erottaa esitystavan ja tietosisällön toisistaan, mutta vain näiden vuosittaisten vakiotietojen osalta.

Joudun joka tapauksessa kirjoittamaan ulkoisen lähdetiedoston itse. Vaikka tiedot periaatteessa löytyvätkin netistä, ne ovat hajallaan eri paikoissa monessa eri muodossa ja vaativat suomenkielistä prosessointia, joten suoraan alkuperäiseltä lähteeltä niitä ei voi impata.

Kuten kysymyksessä kerroin, löydän kyllä ohjeet siihen millä mekanismilla ulkoiset tiedot tuodaan asiakirjaan sitten kun niiden talletustapa on päätetty, mutta en voi valita tuontimekanismia ennen kuin tiedän minkälaista talletustapaa minun kannattaisi suunnitella. Tähän talletustavan suunnitteluun tarvitsen apua.

HTML-muotoisen lähteen etuna olisi se, että siihen on helpompaa upottaa linkkejä alkuperäisiin lähteisiin (Calc-taulukossa sekä URLien lisääminen että valmiiden URLien käyttö on hankalampaa). Mutta jos Calc vaatii että lähteestä haetaan nimenomaan taulukko, ja se taulukko sitten toistetaan yhtenäisenä alueena Calcissa, niin tämä ei käy mun tarkoitukseen ollenkaan.

Tarvitsen sellaisen talletustavan, jossa ulkoisen lähteen tietoja voisi käyttää Calcissa sinne tänne ripoteltuna. Ainoa vaihtoehto taitaa olla toinen Calc-asiakirja, jonka tietoja linkitetään yksitellen solu kerrallaan?

[mpiso]

Minäkään en tiedä muuta vaihtoehtoa kuin toinen Calc-asiakirja. Eli viittaus tulisi soluun tähän tapaan:

Koodia: [Valitse]

='file:///home/mpiso/Lahde1.ods'#Taulukko1.C1
Tuo oli kuitenkin niin ilmeinen vaihtoehto, etten tullut sitä ajatelleeksi, kun kysyit tallennusmuotoa. Tuossa on erillinen dokumentti, mutta voihan se olla myös vain erillinen välilehti. Tietokanta saataisi myös olla vaihtoehto, mutta minä en ole siihen perehtynyt.

[AimoE]

Eli viittaus tulisi soluun tähän tapaan:

Koodia: [Valitse]

='file:///home/mpiso/Lahde1.ods'#Taulukko1.C1

Ei nyt ihan noin. No jaa, voihan tiedostonimen vaihtaa soluihin yhdellä search & replace -operaatiolla, mutta joku epäsuora lauseke saaattaisi olla parempi. Tai sitten ei. En ole koskaan kokeillut mikä vaikutus sellaisella on suorituskykyyn.

[mpiso]

Epäsuoraviittaus voi olla paikallaan jos kaavan kirjoittaa käsin. Tuo on kuitenkin tehty taulukkolaskennan perinteisellä menetelmällä, eli on yhtä kuin merkki (=) soluun ja kopioitavan kohdan valinta.

Kopiointia ei tarvitse tehdä solu kerrallaan, vaan kopioitavat solut voi valita ”maalaamalla”. Lopuksi (kohdesivulla) pitää kuitenkin painaa Ctrl+Vaihto+Enter. Silloin ohjelma kirjoittaa kaavan näin:

{='file:///home/mpiso/Lahde1.ods'#$Taulukko1.A1:C3}

Tuota (matriisia) muokatessa pitää valita koko alue, eli voi olla parempi kopioida yksi solu ja vetää taulukko suuremmaksi kohdesivulla.

[AimoE]

Olen jo moneen kertaan sanonut että tiedot ovat asiakirjassa (sen yhdelä lomakkeella) ripoteltuna pitkin poikin. Ne eivät muodosta matriisia, jonka voisi maalata. Jos sellainen matriisi olisi, voiisn hakea tiedot .csv-tiedostosta tai HTML-taulukosta. Maalaamista ei muutenkaa tarvita. Search&Replace kohdistuu kyllä koko välilehti/taulukko/lomakkeeseen, jos mitään ei maalata sen sisällä. Pitää vai ruksata se boksi että etsitään myös solujen sisältä.


Olen tällä välin tehnyt yhteenvedon kaikista solunimistä joita tarvitsen. Tein sen jotta osaan nimetä solut niin että voin myöhemmin lisätä uusia kaavoja ja vakioita ilman vanhojen uudelleennimeämistä. Kun tein tätä yhteenvetoa erilliseen Calc-tiedostoon, tajusin että siihen samaan tiedostoon olisi kätevä lisätä vuosikohtaiset sarakkeet vakioiden vuosikohtaisia arvoja varten. Jos vakiot ovat yhdessä Calc-tiedostossa, varsinaiseen pääasiakirjaan ei tarvitse vaihtaa tiedostonimeä, vaan riittää että se osaa poimia tiedot oikeasta sarakkeesa ja oikealta riviltä. Mutta tässä tulikin sellainen hankaluus, että jos tosiaan kokoan vakiot yhteen tiedostoon, ja haluan käyttää sitä samaa aputiedostoa myöskin sarakenimien suunnitteluun, niin minulla on suuri kiusaus sorttailla rivejä eri järjestyksen aina suunnitteluvaiheen mukaan.

Jotta voisin vuosikohtaisesta pääasiakirjasta viitata tällaiseen aputiedostoon niin, että viite-eheys säilyy, niin tarvitsen epäsuoran viittauksen, jossa

• tiedostonimi on aina sama aputiedoston nimi;
• sarake on vuosikohtainen (päätellään pääasiakirjan tiettyyn soluun syötetystä vuosiluvusta);
• rivi voisi olla solunimen mukaan nimetty (siis pääasiakirjan solunimi =  aputiedoston riville annettu aluenimi).

Silloin aputiedoston sorttailu ei sekota viittauksia.

[AimoE]

Calcin "Tiedot"-valikossa on sellainen kohta kuin "XML-lähde", mutta en löydä sille kuvausta https://help.libreoffice.org/Calc/ -sivuilta.

[mpiso]

XML on ilmeisesti juuri sitä tietokantaan liittyvää määritystä johon en ole perehtynyt.

https://support.office.com/fi-fi/article/xml-rakenteiden-m%C3%A4%C3%A4ritt%C3%A4minen-soluihin-xml-m%C3%A4%C3%A4rityksess%C3%A4-ddb23edf-f5c5-4fbf-b736-b3bf977a0c53

[AimoE]

Ei taida onnistua epäsuora viittaus toiseen Calc-tiedostoon. Toisin kuin Ecelissä, Calcin INDIRECT-funktio ei osaa etsiä toisen tiedoston sisältä. Ja vaikka osaisikin, kummankin Calc-tiedoston pitäisi olla auki samaan aikaan.

Olen nyt kokeillut ja kokeillut tuota XML-lähdettä, mutta en saa testitiedoston sisältöä avatuksi. En yhtään keksi mikä on vikana.

[AimoE]

XML on ilmeisesti juuri sitä tietokantaan liittyvää määritystä johon en ole perehtynyt.

https://support.office.com/fi-fi/article/xml-rakenteiden-m%C3%A4%C3%A4ritt%C3%A4minen-soluihin-xml-m%C3%A4%C3%A4rityksess%C3%A4-ddb23edf-f5c5-4fbf-b736-b3bf977a0c53

Tuo on Microsoftin, ei LibreOfficen sivusto.

[mpiso]

Siinä on malliesimerkki miten tekstieditorilla (muistio) luodaan XML tiedosto. Käsittääkseni XML on standardi, eikä Microsoftilla ole omaa versiota.

Kopioin tuolta sivulta tuon Kulut.xml tiedoston aivan kopioi ja liitä menetelmällä ja minulla se toimii. Ei kuitenkaan tunnu kauhean näppärältä, kun jokaisen liitämisen jälkeen yhteys sulkeutuu. Kulut.xml ei kuitenkaan tarvitse olla avattuna.

[AimoE]

Kirjoitin kokeeksi hyvin yksnkertaisen xml-tiedoston:

Koodia: [Valitse]

<?xml version="1.0" encoding="UTF-8" ?>

<vakio>
  <aika>
    <VuodessaKuita>12</VuodessaKuita>
    <VuodessaViikkoja>52</VuodessaViikkoja>
    <VuodessaPäiviä>364</VuodessaPäiviä>
  </aika>
</vakio>

Mutta sen sisältö ei avaudu. Siis kun valitsen tiedoston ja sisällön pitäisi tulla näkyviin, se ei tule. Kokeilin sitten DTD- ja XSD-skeemojen kanssa, mutta sekään ei auttanut (skeeman voi luoda sivulla http://xml.mherman.org/index.php/trang/generate, sitä ei tarvitse kirjoittaa itse, mutta viittaus skeemaan täytyy lisätä xml-tiedostoon).

Mikä voi olla vikana noin pienessä tiedostossa?

[mpiso]

Minä olen niin vanha, että ääköset tietotekniikassa hirvittää. Muuta ä>a.

[AimoE]

No justiin. Sitä alkaa jo tottua siihen että UTF-8 kelpaa joka paikassa.

[AimoE]

Eikä tuo luo linkkiä ulkoisen tiedostoon, se vaan tuo arvon tiedostosta ja tallettaa sen Calciin. Pah.

Jäljellä ei ole yhtään keinoa erottaa tietojen ylläpito ulkoasun ylläpidosta.

[mpiso]

Solujen nimeäminen on järkevä tapa, jos tiedoston muoto muuttuu usein. Minä en jostain syystä ole sitä laajemmin taulukkolaskennassa käyttänyt. En vaikka teknisen laskennan takia käytin pääasiassa Mathcad ohjelmaa, jossa muuttujat nimetään. Taulukkolaskentaversio tuli vasta jos laskentapohjalle löytyi yleisempää käyttöä.

En siis väitä täysin ymmärtäväni sitä, minkälainen dokumentistasi pitäisi tulla. Minä kuitenkin käyttäisin suoria tai epäsuoria viittauksia saman dokumentin eri välilehtien välillä. Silloin tiedot päivittyvät heti. Lisäksi nimettyyn soluun voi viitata, ilman välilehtinimeä kaikilla välilehdillä, myös epäsuorasti.

Kahden dokumentin välillä tuollainen epäsuora nimeen viittaus ei todellakaan toimi. Eli nostan kädet ylös jos tietojen ylläpito ja ulkoasun ylläpito pitää nimenomaan olla kaksi erillistä dokumenttia.

[jarmala]

Eikä tuo luo linkkiä ulkoisen tiedostoon, se vaan tuo arvon tiedostosta ja tallettaa sen Calciin. Pah.

Jäljellä ei ole yhtään keinoa erottaa tietojen ylläpito ulkoasun ylläpidosta.

Kannattanee siis luopua tästä paradigmasta ja yrittää tehdä sama asia jollakin aivan toisella tapaa.

Miksi pitäisi nähdä näin paljon vaivaa sellaisen asian eteen, joka tehdään vain kerran vuodessa? Eikö niitä tietoja voi syöttää suoraan siihen samaan calc-tiedostoon vaikka eri välilehdelle? Vai onko niin, että sillä, joka ne syöttää, ei saa olla oikeuksia siihen calc-tiedostoon? Vai oliko sen syöttätiedoston ulkonäkö tärkeä? Teet calcilla sen näköisen syöttötiedoston ja asia ratkeaa sillä. Eh?

Toisaalta sitten, eikös HTML:ssä ole juuri keinot erottaa sisältö ulkoasusta? Joku cascading style sheets -juttu tai jokin...

[jekku]

[...
Miksi pitäisi nähdä näin paljon vaivaa sellaisen asian eteen, joka tehdään vain kerran vuodessa?

Älä väheksy uteliaisuuden generoimaa motivaatiota

Aika usein tulee iskettyä päätä seinään ihan vain kokeillakseni onnistuuko/osaanko.

[AimoE]

Tämä on tasapainoilua eri tarpeiden välillä.

Valitsin Calcin koska siinä kaikki laskukaavat ovat näkösällä. Saatan ehkä jossain vaiheessa lukita osan kentistä, mutta en halua tehdä sitä niin että kaavat piilotetaan. Tämä paradigma on minulle tärkein.

Se on kyllä totta että jos teen vuosikohtaisen kopion vain kerran vuodessa, niin eihän vakioiden päivitys uuteen kopioon silloin ole iso juttu. Ongelma on kehittelyvaiheessa, jolloin minulla on tarve testata vuosikohtaisia kopioita moneen kertaan uudestaan -- ja siinä, että kehittelytarve voi herätä joka vuosi milloin mistäkin syystä.

No, täytyy nyt sitten yrittää testata vain yhden vuoden laskentaa kerrallaan ja pyrkiä minimoimaan vuosikopioiden tekemistä.


Joo, tää on vaikea ymmrätää jos en kerro mitä olen tekemässä, joten paljastetaan sekin: teen sellaista työkalua, jolla voin pitkin vuotta tarkistaa onko verokortti kohdallaan ja milloin kannattaa tilata muutosverokortti.

Asiakirjassa on muutama eri malli ennakonpidätystiedoille, jotka syötän kuukausikohtaisesti. Malleista tehdään maksajakohtaisia kopioita, joihin tiedot oikeasti syötetään. Syötän näille lomakkeille ensin arvion loppuvuoden tuloista ja sitten pikkuhiljaa päivitän todelliset luvut arvioiden tilalle (käytännössä varmaan harvemmin kuin kerran kuussa). Yhdellä lomakkeella kokoan  maksajakohtaisista lomakkeista tiedot yhteen, edelleen kuukausikohtaisesti. Mukana on myös syöttökentät sellaisille tuloille joille ei kannata tehdä maksajakohtaista lomaketta mutta joita haluan seurata kuukausittain.
Tällä lomakkeella näkyy vertailua varten myös lopullisen verotuksen lopputulos, jonka avulla voin arvioida kuinka paljon arvioitu ennakonpidätys eroaa arvioidusta lopputuloksesta.

Näiden ennakonpidätystä seuraavien lomakkeiden jälkeen on veroilmoituslomake, johon haen ennakonpidätystiedot edelliseltä lomakkeelta, ja lisäksi syötän kuluja joita ei kannata seurata kuukausittain (työmatkakulut, yms) Tämä on hyvin suppea, tiivistetty esitys; oikeassa veroilmoituksessa tiedot eritellään paljon tarkemmin.

Tärkein lomake on se, jossa lopullinen vero lasketaan. Tällä lomakkeella ei ole syöttökenttiä, siinä on vain laskukaavoja. Se hakee laskennan lähtötiedot tiedot veroilmoitus-lomakkeelta sekä verovakiot-lomakkeelta, jolla on vuosikohtaiset verojen ja vähennysten prosentit, tulo- tai kulurajat, jne.

Kun teen asiakirjasta kaikkine lomakkeineen vuosikohtaisen kopion, joudun päivittämään verovakioiden lisäksi ne kaavat, joihin lakimuutokset vaikuttavat. Vuosien 2017-2019 aikana lakimuutokset vaikuttavat vain Yle-veron laskentaan (muuttui 2017-2018 välillä) ja ennakonpidätykseen (vuonna 2019 verokorttimalleja on vähemmän ja ne ovat yksinkertaisempia). Tai voihan olla että muutoksia olisi enemmänkin, mutta ei sellaisia jotka vaikuttavat juuri omien verojeni laskentaan. Joka tapauksessa kaavojen päivitys näiden vuosien välillä on vakioiden syöttöön verrattuna helppo homma.

Varsinainen ongelma nyt kehittelyvaiheessa on se, että voin testata verotuslaskennan varmoja oikeita tietoja vasten vain vuoden 2017 tai sitä aiemmilla tiedoilla (mutta vuotta 2017 vanhempien vuosien laskusääntöjä en edes yritä kaivaa esiin). Vuoden 2018 verotus valmistuu vasta 2019 kesällä. Tähtäimeni on vuoden 2019 verokortin tarkistuksessa, mutta homman testaamiseen tarvitsen siis vuoden 2017 tiedot, enkä aio vuotta 2018 myöskään hypätä yli.

Kuulostaa isolta hommalta, mutta ei se kovin iso ole, koska otan laskentaan mukaan vain sen osan joka koskee minua ja jonka siksi voin tarkistaa omista papereistani, mikä on hyvin pieni osa koko verotuslaskennan kokonaisuudesta.

Lisään tähän liitteeksi hyvin lyhykäisen koosteen siitä mitä kaikkia eroja eri tulolajien verotuksessa pitäisi ottaa huomioon jos haluaisi palvella kaikkia mahdollisia tarpeita; kooste rajoittuu vain niihin tuloihin, joita ennakonpidätys koskee.