cancel
Showing results for 
Search instead for 
Did you mean: 

ZeroColon is awesome! but could still use some pointers on

ag1
New Contributor
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

2 REPLIES 2

jim1
New Contributor
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:


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
New Contributor
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