ZeroColon is awesome! but could still use some pointers on

[ag1]

I unfortunately discovered ZeroColon late! it would have saved me a lot of time

url:"http://download.finance.yahoo.com/d/quotes.csv?s=FTR+CTL+LVLT+T+VZ&f=sr"

a:.Q.hg`$url

a1:"s,pe\n",a

t:("SS";enlist",")0:a1

q)t

s    pe   

----------

FTR  N/A  

CTL  14.43

LVLT 5.58 

T    19.70

VZ   15.26

Here is my code for converting a yahoo csv into a q table.

Let's assume that ZeroColon doesn't exist, I still feel that my conversion from g to h isn't optimal.

any pointers?

// here was my original attempt

q)url:"http://download.finance.yahoo.com/d/quotes.csv?s=FTR+CTL+LVLT+T+VZ&f=sr"

q)a:.Q.hq`:url

q)a

"\"FTR\",N/A\n\"CTL\",14.43\n\"LVLT\",5.58\n\"T\",19.70\n\"VZ\",15.26\n"

q)b:"\n" vs a   / return list of strings (delimited by \n) 

q)b

"\"FTR\",N/A"

"\"CTL\",14.43"

"\"LVLT\",5.58"

"\"T\",19.70"

"\"VZ\",15.26"

""

q)c:-1_b    / drop the last empty string

q)c

"\"FTR\",N/A"

"\"CTL\",14.43"

"\"LVLT\",5.58"

"\"T\",19.70"

"\"VZ\",15.26"

q)d:{ssr[x;"\"";""]} each c  / remove "

q)d

"FTR,N/A"

"CTL,14.43"

"LVLT,5.58"

"T,19.70"

"VZ,15.26"

q)e:{ssr[x;"N/A";"0"]} each d / remove N/A

q)e

"FTR,0"

"CTL,14.43"

"LVLT,5.58"

"T,19.70"

"VZ,15.26

q)f:{"," vs x} each e

q)f

"FTR"  ,"0"   

"CTL"  "14.43"

"LVLT" "5.58" 

,"T"   "19.70"

"VZ"   "15.26"

q)g:{"S"$x} each f

q)g

FTR  0    

CTL  14.43

LVLT 5.58 

T    19.70

VZ   15.26

q)h:flip g

q)h

FTR CTL   LVLT T     VZ   

0   14.43 5.58 19.70 15.26

q)t:flip `s`pe!(h 0 ; h 1)

q)t

s    pe   

----------

FTR  0    

CTL  14.43

LVLT 5.58 

T    19.70

VZ   15.26

[jim1]

I would avoid using symbol types for the prices. It would be particularly problematic for large datasets when splaying or partitioning to disk, as you would end up with a bloated symbol file. See here for more details:

http://www.timestored.com/kdb-guides/strings-symbols-enumeration

Here's a one-liner, with prices as floats:

q)update pe:0^pe from flip `s`pe!("SF"; ",") 0: .Q.hg`$url

s    pe   

----------

FTR  0    

CTL  14.43

LVLT 5.58 

T    19.7 

VZ   15.26

[thomas_smyth]

There's a few things you could do without 0:. For starters I'd replace ssr with except because you're just removing "\"" each time:

a:.Q.hg hsym `$url

b:a except "\""

"FTR,N/A\nCTL,21.11\nLVLT,30.88\nT,19.70\nVZ,15.26\n"

From here we can use vs to get closer to the final table:

c:"," vs/: "\n" vs -1_b

"FTR"  "N/A"

"CTL"  "21.11"

"LVLT" "30.88"

,"T"   "19.70"

"VZ"   "15.26"

We can then apply the column names:

d:flip `s`pe!flip c

s      pe

--------------

"FTR"  "N/A"

"CTL"  "21.11"

"LVLT" "30.88"

,"T"   "19.70"

"VZ"   "15.26"

And finally convert types:

update `$s, 0^"F"$pe from d

s    pe

----------

FTR  0

CTL  21.11

LVLT 30.88

T    19.7

VZ   15.26

Of course, using 0: will still be a better solution.

Regards,

Thomas Smyth

AquaQ Analytics