Yahoo market data importeren in MySQL

[Verwijderd]

Ik moet een hoop market data importeren in een MySQL database. De data haal ik van Yahoo Finance (csv alert). Een voorbeeld van een CSV van daar ziet er als volgt uit:

code:

2011-08-11,370.52,375.45,364.72,373.70,26482900,373.70
2011-08-10,371.15,374.65,362.50,363.69,31358700,363.69
2011-08-09,361.30,374.61,355.00,374.01,38629400,374.01
2011-08-08,361.69,367.77,353.02,353.21,40810600,353.21

Nu wil ik even zeker weten dat ik de tabel waar deze informatie inkomt, goed gedefinieerd heb:

SQL:

CREATE TABLE prices (
  `date` varchar(10) NOT NULL,
  `open` float NOT NULL,
  high float NOT NULL,
  low float NOT NULL,
  `close` float NOT NULL,
  volume int(8) NOT NULL,
  `adj-close` float NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Een aantal vragen waar ik mee zit zijn of (1) het niet beter is om de datum bij importeren om te zitten in een UNIX timestamp (dat is in al mijn andere tabellen wel zo) en of (2) een float de juiste keuze is om de prijzen in op te slaan. Graag commentaar

[ Voor 5% gewijzigd door Verwijderd op 28-10-2011 13:54 ]

[edxtreem]

1. ja
2. dan kun je wel met afrondingsfouten te maken krijgen, ik weet niet of mysql ook een valuta type ondersteund zo niet dan zou je de prijzen nog naar integers kunnen converteren (*100).

Persoonlijk zou ik ook voor de innodb engine kiezen mocht je nog referentiële integriteit willen afdwingen in je database structuur wat myisam voor zo ver ik weet niet ondersteund. Afhankelijk van de encoding van je andere tabellen zou ik ook gelijk voor de UTF8 charset gaan.

[Remus]

Gebruik gewoon het juiste datetype voor de juiste data:
Voor een datum een DATE, geen VARCHAR(10), geen BIGINTEGER, gewoon een DATE daar is dat datatype voor bedoeld.
En gebruik voor financiële data geen float, maar een DECIMAL(18,2) of zoals edxtreem zegt: gewoon een integer * 100 (al is het DECIMAL datatype specifiek bedoeld voor dit soort gegevens.

[dik_voormekaar]

Een datum moet je definieren als DATETIME, niet als TIMESTAMP.
Die laatste begint pas bij 1970 (en houdt op in 2038), dus als je nog eens koersen van voor die tijd wilt opslaan, kan dat niet.

[Remus]

dik_voormekaar schreef op vrijdag 28 oktober 2011 @ 14:10:
Een datum moet je definieren als DATETIME, niet als TIMESTAMP.
Die laatste begint pas bij 1970 (en houdt op in 2038), dus als je nog eens koersen van voor die tijd wilt opslaan, kan dat niet.

Als je geen time-deel nodig hebt is het efficienter om voor DATE te gaan.

[dik_voormekaar]

Dat klopt.

[Verwijderd]

Bedankt voor de reakties! Even een update:

SQL:

CREATE TABLE prices (
  `date` date NOT NULL,
  `open` decimal(18,2) NOT NULL,
  high decimal(18,2) NOT NULL,
  low decimal(18,2) NOT NULL,
  `close` decimal(18,2) NOT NULL,
  volume int(8) NOT NULL,
  `adj-close` decimal(18,2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Tot nu toe heb ik altijd MyISAM gekozen als engine; ik zal eens kijken naar INNODB

[ Voor 10% gewijzigd door Verwijderd op 28-10-2011 14:20 ]

[RobIII]

Ik zou niet voor decimal gaan (en al helemaal niet zo huge-ass) maar gewoon voor integers.
Getallen en talstelsels FAQ -> Fixed point
Sowieso is 't zuiniger in opslag waardoor er meer records in een page (en dus in 't geheugen) passen, indices efficiënter kunnen etc. en daarbij vraag ik me af hoe MySQL Decimal intern behandelt; dat kan best wel eens heel beroerd zijn (zie hier)

[ Voor 61% gewijzigd door RobIII op 28-10-2011 14:29 ]

[P_de_B]

Wat is het voordeel van integers ten opzichte van decimal volgens jou als je bedragen wilt opslaan? Dan moet je nog alles weer delen door honderd als je twee decimalen hebt?

[RobIII]

P_de_B schreef op vrijdag 28 oktober 2011 @ 14:25:
Wat is het voordeel van integers ten opzichte van decimal volgens jou als je bedragen wilt opslaan?

Dat integers zéér waarschijnlijk (maar pin me er niet op vast, ik ken de MySQL internals niet) efficiënter zijn in opslag, verwerking e.d. (zie ook mijn edit).

P_de_B schreef op vrijdag 28 oktober 2011 @ 14:25:
Dan moet je nog alles weer delen door honderd als je twee decimalen hebt?

a) Probleem van de presentatielaag; boeie
b) Als je in euro's/dollars rekent heb je inderdaad decimalen. Reken nu eens in eurocenten/dollarcents (of kleinste eenheid)

[ Voor 9% gewijzigd door RobIII op 28-10-2011 14:29 ]

[Verwijderd]

RobIII schreef op vrijdag 28 oktober 2011 @ 14:24:
Ik zou niet voor decimal gaan (en al helemaal niet zo huge-ass) maar gewoon voor integers.
Getallen en talstelsels FAQ -> Fixed point
Sowieso is 't zuiniger in opslag waardoor er meer records in een page (en dus in 't geheugen) passen, indices efficiënter kunnen etc. en daarbij vraag ik me af hoe MySQL Decimal intern behandelt; dat kan best wel eens heel beroerd zijn (zie hier)

Prior to MySQL 5.0.3, the DECIMAL type was stored as a string and would typically be slower. However, since MySQL 5.0.3 the DECIMAL type is stored in a binary format so with the size of your DECIMAL above, there may not be much difference in performance. Bron

Dan kies ik toch liever voor een DECIMAL ipv een INTEGER, omdat ik daarmee niet in mijn presentatielaag hoef te vermenigvuldigen met / delen door 100

[P_de_B]

RobIII schreef op vrijdag 28 oktober 2011 @ 14:28:
[...]

Dat integers zéér waarschijnlijk (maar pin me er niet op vast, ik ken de MySQL internals niet) efficiënter zijn in opslag, verwerking e.d. (zie ook mijn edit).


[...]

a) Probleem van de presentatielaag; boeie

Tja, blijft een probleem van de applicatie. Of het nu in de presentatielaag zit of in de database, als ontwikkelaar moet je daar rekening mee houden. Ik denk dat de eventuele winst die je haalt bij het efficienter opslaan zeker verloren gaat bij het rekenen. Ik zou de extra compexiteit die het meebrengt niet verkiezen boven een eventuele minimale efficiencywinst bij het opslaan.

b) Als je in euro's/dollars rekent heb je inderdaad decimalen. Reken nu eens in eurocenten/dollarcents (of kleinste eenheid)

Ja, snap ik. Maar de eindgebruiker heeft daar niks mee nodig. Die wil geen prijs in centen. Zie hierboven onder "extra complexiteit"


en je weet wat er gezegd is he, over editten

[RobIII]

P_de_B schreef op vrijdag 28 oktober 2011 @ 15:58:
Ik denk dat de eventuele winst die je haalt bij het efficienter opslaan zeker verloren gaat bij het rekenen

P_de_B schreef op vrijdag 28 oktober 2011 @ 15:58:
Ja, snap ik. Maar de eindgebruiker heeft daar niks mee nodig. Die wil geen prijs in centen. Zie hierboven onder "extra complexiteit"

Het slaat zo goed als zeker efficiënter als integer op; het rekent (op ALU niveau zeg maar) at best _{forget it} even snel als een integer. Verder hoef je bij berekeningen helemaal nergens geen rekening mee te houden. Het enige waar je rekening mee moet houden is dat je bij 't weegeven een "FormatCurrency" functie oid gebruikt die even deelt door honderd en dat je bij input (formulieren e.d.) de ingevoerde waardes meteen weer naar centen rekent. Het is dus:

PHP:

function FormatCurrency($amount, $valuta = "€") {
  return $valuta . ' '  . number_format($amount,2);
}
//vs:
function FormatCurrency($amount, $valuta = "€") {
  return $valuta . ' '  . number_format($amount/100,2);
}

In een query is, bijvoorbeeld, Sum(bedrag_in_centen) is exact hetzelfde als Sum(bedrag_in_euros) zolang je presentatielaag de deling door 100 afhandelt. Als het goed is gebruik je toch al een functie voor 't formatteren van een bedrag; als je daar nog even door 100 deelt ben je er.

Echter, intern, zal een Sum(bedrag_in_centen) zeer waarschijnlijk beter presteren dan een Sum(bedrag_in_euros) wanneer die laatste een decimal is; dat datatype heeft gewoon overhead (groter, zwaarder bij bewerken). Punt.

Neemt niet weg dat we het hebben over een microoptimalisatie, daar niet van. Maar het is nou niet alsof je vreselijk moeilijk moet doen of enorme complexiteit toevoegt. Die ene keer dat je perongeluk de centen weergeeft i.p.v. de euro's in je presentatielaag weet je instantaan wat er aan de hand is.

tl;dr: Ik zie dus niet waar die 'extra complexiteit' zou zitten noch waarom een eindgebruiker met centen te maken zou krijgen zolang je presentatielaag 't fatsoenlijk afhandelt. Micro-optimalisatie? True. Dat wel.

[_js_]

Maar dan kom je weer op volgende problemen, sommige aandelen hebben een prijs met 3 cijfers achter de komma en moet je je hele database aanpassen als je die ook wilt ondersteunen. Het lijkt me beter om dan toch de echte prijs te gebruiken, inclusief komma's. Want er zal vast ook ooit een aandeel komen dat met 4 cijfers achter de komma in de boeken komt waardoor je weer je alle waarden in je database kunt aanpassen.

[RobIII]

_js_ schreef op vrijdag 28 oktober 2011 @ 16:49:
Maar dan kom je weer op volgende problemen, sommige aandelen hebben een prijs met 3 cijfers achter de komma en moet je je hele database aanpassen als je die ook wilt ondersteunen. Het lijkt me beter om dan toch de echte prijs te gebruiken, inclusief komma's. Want er zal vast ook ooit een aandeel komen dat met 4 cijfers achter de komma in de boeken komt waardoor je weer je alle waarden in je database kunt aanpassen.

Of je hanteert een factor 1000(0) i.p.v. 100
Either way, en dat wil ik nog eens benadrukken, het is lood om oud ijzer maar ik denk als je gaat benchen op een paar miljoen records dat 't best meetbaar is (merkbaar is een tweede).

[P_de_B]

Ja, maar als je een andere factor gaat gebruiken moet je dus alle oude waardes ook updaten. O.a. dat bedoel ik met extra complexiteit. Je kunt niet gewoon de waarde van je database voor waar aannemen, je moet er nog wat mee.

Hier was zo'n mooie entry op TDWTF voor, maar ik kan hem niet vinden

[P_de_B]

RobIII en ik hebben ieder nog even getest met een tabel met enkele tientallen miljoenen records. Een Average van de waarden in een kolom met een integer datatype wordt ongeveer in de helft van de tijd berekend als die van een kolom met decimale waarde. Dat is toch best een significant verschil. Wellicht iets om rekening mee te houden