1 people like it.

store yahoo quotes in kdb

This snippet will download the constituents from ftse, stoxx50e,dji,hsi,gspc and store quotes beginning from 1981 into kdb. To get this to work you need to download kpnet from http://kpnet.codeplex.com/ and kdb. 

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
30: 
31: 
32: 
33: 
34: 
35: 
36: 
37: 
38: 
39: 
40: 
41: 
42: 
43: 
44: 
45: 
46: 
47: 
48: 
49: 
50: 
51: 
52: 
53: 
54: 
55: 
56: 
57: 
58: 
59: 
60: 
61: 
62: 
63: 
64: 
65: 
66: 
67: 
68: 

// rference to the dll from KpNet to build connection to kdb.
#r @"C:\q\kpnet\release\KpNet.KdbPlusClient.dll"

open System
open System.Net
open System.Text.RegularExpressions

let matcher input  = Regex.Matches(input ,"\"/q\?s=(.*?)\"")

let webClient = new WebClient()

let numperpage = 51
let indexurl sym num= "http://finance.yahoo.com/q/cp?s="+sym+"&c="+num.ToString()
// lets download all the constituents from these index
let rawData = [("^GSPC",500); ("^FTSE",100); ("^HSI",45);("^STOXX50E",50);("^DJI",30)]

let rawSym = rawData |> List.fold(
                            fun list (index,num) -> 
                                let nnn = num / numperpage
                                let nn = nnn-1
                                let n = max nn 0
                                let pages = seq{ for i in [0 .. n ]  -> indexurl index i} |> Seq.toList
                                let result = pages |> Seq.fold( fun listToAppend url -> 
                                                        let matches = matcher(webClient.DownloadString(url))
                                                        let r = [for m in matches -> (m.Groups.Item 1).ToString()] |> List.tail
                                                        r |> List.append listToAppend
                                              ) List.empty<string>
                                (index,result)::list
                        ) List.empty<string*string list>

//change the parameters according to your settings.
let kdb = KpNet.KdbPlusClient.KdbPlusDatabaseClient.Factory.CreateNonPooledClient("server=localhost;port=1444")

// we save the constituents into a kdb
// needs to be extended with date, because the constiuents might changed.
let constituens = "constituens"
kdb.ExecuteNonQuery(constituens+":()!()" );
rawSym |> List.iter(fun (index,ll) ->
                         let data = constituens+"[`$"+"\""+index+"\""+"]"
                         ll  |> List.iter( fun c -> kdb.ExecuteNonQuery(data+":"+data+",enlist `$\""+c+"\"" ) ) 

                   )

let table = "yahooQuotes"
let cols = "`sym`xdate`open`high`low`close`volume`adjclose"

kdb.ExecuteNonQuery(table+":([] sym:`$();xdate:`date$();open:`float$();high:`float$();low:`float$();close:`float$();volume:`int$();adjclose:`float$())" );

let urlleft,urlright=  "http://ichart.finance.yahoo.com/table.csv?s=","&a=00&b=3&c=1984&d=04&e=25&f=2011&ignore=.csv"

// now, download all the quotes
let datas = rawSym 
            |> List.fold(fun result (_,l) -> result |> List.append l) List.empty<string>
            |> List.iter ( fun s -> 
    try
        let d = webClient.DownloadString(urlleft + s + urlright).Split[|'\n'|] |> Array.toList |> List.tail
        d |> List.iter( fun l -> match l with
                                 | "" -> ()
                                 | _ -> let line = s+","+l
                                        kdb.ExecuteNonQuery(table+",:"+cols+"!(\"SDFFFFIF \";\",\")0:"+"\""+line+"\"")
            )
        
    with ex -> printfn "%A" ex
    )
kdb.ExecuteNonQuery("save `yahooQuotes")
;;
   
 // Isn't this cool?
namespace System
namespace System.Net
namespace System.Text
namespace System.Text.RegularExpressions
val matcher : input:string -> MatchCollection

Full name: Script.matcher
val input : string
Multiple items
type Regex =
  new : pattern:string -> Regex + 1 overload
  member GetGroupNames : unit -> string[]
  member GetGroupNumbers : unit -> int[]
  member GroupNameFromNumber : i:int -> string
  member GroupNumberFromName : name:string -> int
  member IsMatch : input:string -> bool + 1 overload
  member Match : input:string -> Match + 2 overloads
  member Matches : input:string -> MatchCollection + 1 overload
  member Options : RegexOptions
  member Replace : input:string * replacement:string -> string + 5 overloads
  ...

Full name: System.Text.RegularExpressions.Regex

--------------------
Regex(pattern: string) : unit
Regex(pattern: string, options: RegexOptions) : unit
Regex.Matches(input: string, pattern: string) : MatchCollection
Regex.Matches(input: string, pattern: string, options: RegexOptions) : MatchCollection
val webClient : WebClient

Full name: Script.webClient
Multiple items
type WebClient =
  inherit Component
  new : unit -> WebClient
  member BaseAddress : string with get, set
  member CachePolicy : RequestCachePolicy with get, set
  member CancelAsync : unit -> unit
  member Credentials : ICredentials with get, set
  member DownloadData : address:string -> byte[] + 1 overload
  member DownloadDataAsync : address:Uri -> unit + 1 overload
  member DownloadFile : address:string * fileName:string -> unit + 1 overload
  member DownloadFileAsync : address:Uri * fileName:string -> unit + 1 overload
  member DownloadString : address:string -> string + 1 overload
  ...

Full name: System.Net.WebClient

--------------------
WebClient() : unit
val numperpage : int

Full name: Script.numperpage
val indexurl : sym:string -> num:'a -> string

Full name: Script.indexurl
val sym : string
val num : 'a
Object.ToString() : string
val rawData : (string * int) list

Full name: Script.rawData
val rawSym : (string * string list) list

Full name: Script.rawSym
Multiple items
module List

from Microsoft.FSharp.Collections

--------------------
type List<'T> =
  | ( [] )
  | ( :: ) of Head: 'T * Tail: 'T list
  interface IEnumerable
  interface IEnumerable<'T>
  member Head : 'T
  member IsEmpty : bool
  member Item : index:int -> 'T with get
  member Length : int
  member Tail : 'T list
  static member Cons : head:'T * tail:'T list -> 'T list
  static member Empty : 'T list

Full name: Microsoft.FSharp.Collections.List<_>
val fold : folder:('State -> 'T -> 'State) -> state:'State -> list:'T list -> 'State

Full name: Microsoft.FSharp.Collections.List.fold
Multiple items
val list : (string * string list) list

--------------------
type 'T list = List<'T>

Full name: Microsoft.FSharp.Collections.list<_>
val index : string
val num : int
val nnn : int
val nn : int
val n : int
val max : e1:'T -> e2:'T -> 'T (requires comparison)

Full name: Microsoft.FSharp.Core.Operators.max
val pages : string list
Multiple items
val seq : sequence:seq<'T> -> seq<'T>

Full name: Microsoft.FSharp.Core.Operators.seq

--------------------
type seq<'T> = Collections.Generic.IEnumerable<'T>

Full name: Microsoft.FSharp.Collections.seq<_>
val i : int
module Seq

from Microsoft.FSharp.Collections
val toList : source:seq<'T> -> 'T list

Full name: Microsoft.FSharp.Collections.Seq.toList
val result : string list
val fold : folder:('State -> 'T -> 'State) -> state:'State -> source:seq<'T> -> 'State

Full name: Microsoft.FSharp.Collections.Seq.fold
val listToAppend : string list
val url : string
val matches : MatchCollection
WebClient.DownloadString(address: Uri) : string
WebClient.DownloadString(address: string) : string
val r : string list
val m : Match
property Match.Groups: GroupCollection
Multiple items
property GroupCollection.Item: int -> Group

--------------------
property GroupCollection.Item: string -> Group
val tail : list:'T list -> 'T list

Full name: Microsoft.FSharp.Collections.List.tail
val append : list1:'T list -> list2:'T list -> 'T list

Full name: Microsoft.FSharp.Collections.List.append
val empty<'T> : 'T list

Full name: Microsoft.FSharp.Collections.List.empty
Multiple items
val string : value:'T -> string

Full name: Microsoft.FSharp.Core.Operators.string

--------------------
type string = String

Full name: Microsoft.FSharp.Core.string
val kdb : obj

Full name: Script.kdb
val constituens : string

Full name: Script.constituens
val iter : action:('T -> unit) -> list:'T list -> unit

Full name: Microsoft.FSharp.Collections.List.iter
val ll : string list
val data : string
val c : string
val table : string

Full name: Script.table
val cols : string

Full name: Script.cols
val urlleft : string

Full name: Script.urlleft
val urlright : string

Full name: Script.urlright
val datas : unit

Full name: Script.datas
val l : string list
val s : string
val d : string list
type Array =
  member Clone : unit -> obj
  member CopyTo : array:Array * index:int -> unit + 1 overload
  member GetEnumerator : unit -> IEnumerator
  member GetLength : dimension:int -> int
  member GetLongLength : dimension:int -> int64
  member GetLowerBound : dimension:int -> int
  member GetUpperBound : dimension:int -> int
  member GetValue : [<ParamArray>] indices:int[] -> obj + 7 overloads
  member Initialize : unit -> unit
  member IsFixedSize : bool
  ...

Full name: System.Array
val toList : array:'T [] -> 'T list

Full name: Microsoft.FSharp.Collections.Array.toList
val l : string
val line : string
val ex : exn
val printfn : format:Printf.TextWriterFormat<'T> -> 'T

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.printfn
Raw view Test code New version

More information

Link:http://fssnip.net/4l
Posted:12 years ago
Author:Kim Tang
Tags: yahoo quotes , kdb

This web site is created using F# and Suave web server. It is hosted on Azure and the source code is on GitHub. Contributions are welcome!

The first version of fssnip.net has been created by @tomaspetricek back in 2010. This web site is a new, open-source and contribution-friendly version.