ask for help for vba to kdb+

Community Support
1

Is there a way to pass some values from vba to kdb+ and fetch a
results real time please?
I want to update kdb+ database with values by vba and get a computed
result in the return.For example a vba function:
kdbinterf(kdbserver,dataset,var1,var2,var3…,result)
or
result=kdbinterf(kdbserver,dataset,var1,var2,var3…)
I think odbc/jdbc not very fast,so is there a way?

Thank you so much!

2

take a look at https://code.kx.com/trac/wiki/Cookbook/InterfacingWithCSharp.should be usable via .net.On Nov 8, 8:26?am, BAB <buyatb…> wrote:> Is there a way to pass some values from vba to kdb+ and fetch a> results real time please?> I want to update kdb+ database with values by vba and get a computed> result in the return.For example a vba function:> kdbinterf(kdbserver,dataset,var1,var2,var3…,result)> or> result=kdbinterf(kdbserver,dataset,var1,var2,var3…)> I think odbc/jdbc not very fast,so is there a way?>> Thank you so much!</buyatb…>

3

For real time updates in Excel you can use Charlie’s K4RTDServer
(available here:
https://code.kx.com/trac/browser/contrib/cskelton/excelrtd). But it
requires tick on q side and it can update values only on your sheet
(not in vba).

The easiest way for passing values from vba to kdb+ is to write your
own dynamic library using C interface for kdb+
(https://code.kx.com/trac/wiki/Cookbook/InterfacingWithC). Header of
your function in C might look similar to the one below:
VARIANT kdbinterf(char *kdbserver, VARIANT *dataset, int var1, …);

Then you can import such funtion to the VBA by:
Public Declare Function kdbinterf Lib “YourLib.dll” (ByVal kdbserver
As String, ByRef dataset As Variant, ByVal var1 As Long, …) As
Variant // YourLib.dll has to be on PATH (you can also specify full
path).

Actually, in your C code you have to translate input parameters to K
objects (see struct k0 in “Interfacing with C”), then you can send
query using k() function, and again translate K object (result) to
desired data type and return it to the VBA.

I hope it will help you.

Cheers,
Kamil.

4

Thank you guys!For RTDServer, the refresh frequence is 2s,isn’t it?And I know VBA can call C/C++ based dll.But I am not very good at C.So could somebody make a dll project please?A dll just contains a function: kdbinterf(char *kdbserver, VARIANT*dataset, int var1, …,Result_var_in_kdb);and returns the value in kdb after the computed by the intvar1, …,Result_var_in_kdbThank you!On Nov 9, 5:34?am, Kamil Ja?kiewicz <jaskiewicz.ka…>wrote:> For real time updates in Excel you can use Charlie’s K4RTDServer> (available here:https://code.kx.com/trac/browser/contrib/cskelton/excelrtd). But it> requires tick on q side and it can update values only on your sheet> (not in vba).>> The easiest way for passing values from vba to kdb+ is to write your> own dynamic library using C interface for kdb+> (https://code.kx.com/trac/wiki/Cookbook/InterfacingWithC). Header of> your function in C might look similar to the one below:> VARIANT kdbinterf(char *kdbserver, VARIANT *dataset, int var1, …);>> Then you can import such funtion to the VBA by:> Public Declare Function kdbinterf Lib “YourLib.dll” (ByVal kdbserver> As String, ByRef dataset As Variant, ByVal var1 As Long, …) As> Variant ? // YourLib.dll has to be on PATH (you can also specify full> path).>> Actually, in your C code you have to translate input parameters to K> objects (see struct k0 in “Interfacing with C”), then you can send> query using k() function, and again translate K object (result) to> desired data type and return it to the VBA.>> I hope it will help you.>> Cheers,> Kamil.>> On 8 November 2010 14:26, BAB <buyatb…> wrote:>>>> > Is there a way to pass some values from vba to kdb+ and fetch a> > results real time please?> > I want to update kdb+ database with values by vba and get a computed> > result in the return.For example a vba function:> > kdbinterf(kdbserver,dataset,var1,var2,var3…,result)> > or> > result=kdbinterf(kdbserver,dataset,var1,var2,var3…)> > I think odbc/jdbc not very fast,so is there a way?>> > Thank you so much!>> > –> >

Submitted via Google Groups</buyatb…></jaskiewicz.ka…>

5

And if there is a COM/ActiveX based dll.Best!!Cheers!On Nov 10, 12:21?pm, BAB <buyatb…> wrote:> Thank you guys!> For RTDServer, the refresh frequence is 2s,isn’t it?> And I know VBA can call C/C++ based dll.> But I am not very good at C.> So could somebody make a dll project please?> A dll just contains a function: kdbinterf(char *kdbserver, VARIANT> *dataset, int var1, …,Result_var_in_kdb);> and returns the value in kdb after the computed by the int> var1, …,Result_var_in_kdb> Thank you!>> On Nov 9, 5:34?am, Kamil Ja?kiewicz <jaskiewicz.ka…>> wrote:>>>> > For real time updates in Excel you can use Charlie’s K4RTDServer> > (available here:https://code.kx.com/trac/browser/contrib/cskelton/excelrtd). But it> > requires tick on q side and it can update values only on your sheet> > (not in vba).>> > The easiest way for passing values from vba to kdb+ is to write your> > own dynamic library using C interface for kdb+> > (https://code.kx.com/trac/wiki/Cookbook/InterfacingWithC). Header of> > your function in C might look similar to the one below:> > VARIANT kdbinterf(char *kdbserver, VARIANT *dataset, int var1, …);>> > Then you can import such funtion to the VBA by:> > Public Declare Function kdbinterf Lib “YourLib.dll” (ByVal kdbserver> > As String, ByRef dataset As Variant, ByVal var1 As Long, …) As> > Variant ? // YourLib.dll has to be on PATH (you can also specify full> > path).>> > Actually, in your C code you have to translate input parameters to K> > objects (see struct k0 in “Interfacing with C”), then you can send> > query using k() function, and again translate K object (result) to> > desired data type and return it to the VBA.>> > I hope it will help you.>> > Cheers,> > Kamil.>> > On 8 November 2010 14:26, BAB <buyatb…> wrote:>> > > Is there a way to pass some values from vba to kdb+ and fetch a> > > results real time please?> > > I want to update kdb+ database with values by vba and get a computed> > > result in the return.For example a vba function:> > > kdbinterf(kdbserver,dataset,var1,var2,var3…,result)> > > or> > > result=kdbinterf(kdbserver,dataset,var1,var2,var3…)> > > I think odbc/jdbc not very fast,so is there a way?>> > > Thank you so much!>> > > –> > >

Submitted via Google Groups</buyatb…></jaskiewicz.ka…></buyatb…>

6

On 10 November 2010 05:21, BAB wrote:
> Thank you guys!
> For RTDServer, the refresh frequence is 2s,isn’t it?
No, it can be modified. See here:
http://msdn.microsoft.com/en-us/library/aa140060(office.10).aspx#odc_xlrtdf=
aq_howconfigrtdthrottle

> And I know VBA can call C/C++ based dll.
> But I am not very good at C.
> So could somebody make a dll project please?
> A dll just contains a function: kdbinterf(char *kdbserver, VARIANT
> *dataset, int var1, …,Result_var_in_kdb);
> and returns the value in kdb after the computed by the int
> var1, …,Result_var_in_kdb
> Thank you!
>
> On Nov 9, 5:34=C2=A0am, Kamil Ja=C5=9Bkiewicz <jaskiewicz.ka…>>
> wrote:
>> For real time updates in Excel you can use Charlie’s K4RTDServer
>> (available here:https://code.kx.com/trac/browser/contrib/cskelton/excelr=
td). But it
>> requires tick on q side and it can update values only on your sheet
>> (not in vba).
>>
>> The easiest way for passing values from vba to kdb+ is to write your
>> own dynamic library using C interface for kdb+
>> (https://code.kx.com/trac/wiki/Cookbook/InterfacingWithC). Header of
>> your function in C might look similar to the one below:
>> VARIANT kdbinterf(char *kdbserver, VARIANT *dataset, int var1, …);
>>
>> Then you can import such funtion to the VBA by:
>> Public Declare Function kdbinterf Lib “YourLib.dll” (ByVal kdbserver
>> As String, ByRef dataset As Variant, ByVal var1 As Long, …) As
>> Variant =C2=A0 // YourLib.dll has to be on PATH (you can also specify fu=
ll
>> path).
>>
>> Actually, in your C code you have to translate input parameters to K
>> objects (see struct k0 in “Interfacing with C”), then you can send
>> query using k() function, and again translate K object (result) to
>> desired data type and return it to the VBA.
>>
>> I hope it will help you.
>>
>> Cheers,
>> Kamil.
>>
>> On 8 November 2010 14:26, BAB <buyatb…> wrote:
>>
>>
>>
>> > Is there a way to pass some values from vba to kdb+ and fetch a
>> > results real time please?
>> > I want to update kdb+ database with values by vba and get a computed
>> > result in the return.For example a vba function:
>> > kdbinterf(kdbserver,dataset,var1,var2,var3…,result)
>> > or
>> > result=3Dkdbinterf(kdbserver,dataset,var1,var2,var3…)
>> > I think odbc/jdbc not very fast,so is there a way?
>>
>> > Thank you so much!
>>
>> > –
>> > You received this message because you are subscribed to the Google Gro=
ups “Kdb+ Personal Developers” group.
>> > To post to this group, send email to personal-kdbplus@googlegroups.com=
.
>> > To unsubscribe from this group, send email to personal-kdbplus+unsubsc=
ribe@googlegroups.com.
>> > For more options, visit this group athttp://groups.google.com/group/pe=
rsonal-kdbplus?hl=3Den.- Hide quoted text -
>>
>> - Show quoted text -
>
> –
>

Submitted via Google Groups</buyatb…></jaskiewicz.ka…>

7

On 10 November 2010 05:21, BAB wrote:
> Thank you guys!
> For RTDServer, the refresh frequence is 2s,isn’t it?
> And I know VBA can call C/C++ based dll.
> But I am not very good at C.
> So could somebody make a dll project please?

You can find example here:
http://hotfile.com/dl/82820449/928c626/qDll.zip.html

dll has four functions:
hopen, hclose, query1 (simple params), query2 (with variant parameters)

For usage of them see Excel workbook from archive.

> A dll just contains a function: kdbinterf(char *kdbserver, VARIANT
> *dataset, int var1, …,Result_var_in_kdb);
> and returns the value in kdb after the computed by the int
> var1, …,Result_var_in_kdb
> Thank you!
>
> On Nov 9, 5:34=C2=A0am, Kamil Ja=C5=9Bkiewicz <jaskiewicz.ka…>>
> wrote:
>> For real time updates in Excel you can use Charlie’s K4RTDServer
>> (available here:https://code.kx.com/trac/browser/contrib/cskelton/excelr=
td). But it
>> requires tick on q side and it can update values only on your sheet
>> (not in vba).
>>
>> The easiest way for passing values from vba to kdb+ is to write your
>> own dynamic library using C interface for kdb+
>> (https://code.kx.com/trac/wiki/Cookbook/InterfacingWithC). Header of
>> your function in C might look similar to the one below:
>> VARIANT kdbinterf(char *kdbserver, VARIANT *dataset, int var1, …);
>>
>> Then you can import such funtion to the VBA by:
>> Public Declare Function kdbinterf Lib “YourLib.dll” (ByVal kdbserver
>> As String, ByRef dataset As Variant, ByVal var1 As Long, …) As
>> Variant =C2=A0 // YourLib.dll has to be on PATH (you can also specify fu=
ll
>> path).
>>
>> Actually, in your C code you have to translate input parameters to K
>> objects (see struct k0 in “Interfacing with C”), then you can send
>> query using k() function, and again translate K object (result) to
>> desired data type and return it to the VBA.
>>
>> I hope it will help you.
>>
>> Cheers,
>> Kamil.
>>
>> On 8 November 2010 14:26, BAB <buyatb…> wrote:
>>
>>
>>
>> > Is there a way to pass some values from vba to kdb+ and fetch a
>> > results real time please?
>> > I want to update kdb+ database with values by vba and get a computed
>> > result in the return.For example a vba function:
>> > kdbinterf(kdbserver,dataset,var1,var2,var3…,result)
>> > or
>> > result=3Dkdbinterf(kdbserver,dataset,var1,var2,var3…)
>> > I think odbc/jdbc not very fast,so is there a way?
>>
>> > Thank you so much!
>>
>> > –
>> > You received this message because you are subscribed to the Google Gro=
ups “Kdb+ Personal Developers” group.
>> > To post to this group, send email to personal-kdbplus@googlegroups.com=
.
>> > To unsubscribe from this group, send email to personal-kdbplus+unsubsc=
ribe@googlegroups.com.
>> > For more options, visit this group athttp://groups.google.com/group/pe=
rsonal-kdbplus?hl=3Den.- Hide quoted text -
>>
>> - Show quoted text -
>
> –
>

Submitted via Google Groups</buyatb…></jaskiewicz.ka…>

8

It’s great!Thx a million!On 11?16?, ??8?20?, Kamil Ja?kiewicz <jaskiewicz.ka…>wrote:> On 10 November 2010 05:21, BAB <buyatb…> wrote:>> > Thank you guys!> > For RTDServer, the refresh frequence is 2s,isn’t it?> > And I know VBA can call C/C++ based dll.> > But I am not very good at C.> > So could somebody make a dll project please?>> You can find example here:http://hotfile.com/dl/82820449/928c626/qDll.zip.html&gt;&gt; dll has four functions:> hopen, hclose, query1 (simple params), query2 (with variant parameters)>> For usage of them see Excel workbook from archive.>>>> > A dll just contains a function: kdbinterf(char *kdbserver, VARIANT> > *dataset, int var1, …,Result_var_in_kdb);> > and returns the value in kdb after the computed by the int> > var1, …,Result_var_in_kdb> > Thank you!>> > On Nov 9, 5:34 am, Kamil Ja?kiewicz <jaskiewicz.ka…>> > wrote:> >> For real time updates in Excel you can use Charlie’s K4RTDServer> >> (available here:https://code.kx.com/trac/browser/contrib/cskelton/excelrtd). But it> >> requires tick on q side and it can update values only on your sheet> >> (not in vba).>> >> The easiest way for passing values from vba to kdb+ is to write your> >> own dynamic library using C interface for kdb+> >> (https://code.kx.com/trac/wiki/Cookbook/InterfacingWithC). Header of> >> your function in C might look similar to the one below:> >> VARIANT kdbinterf(char *kdbserver, VARIANT *dataset, int var1, …);>> >> Then you can import such funtion to the VBA by:> >> Public Declare Function kdbinterf Lib “YourLib.dll” (ByVal kdbserver> >> As String, ByRef dataset As Variant, ByVal var1 As Long, …) As> >> Variant // YourLib.dll has to be on PATH (you can also specify full> >> path).>> >> Actually, in your C code you have to translate input parameters to K> >> objects (see struct k0 in “Interfacing with C”), then you can send> >> query using k() function, and again translate K object (result) to> >> desired data type and return it to the VBA.>> >> I hope it will help you.>> >> Cheers,> >> Kamil.>> >> On 8 November 2010 14:26, BAB <buyatb…> wrote:>> >> > Is there a way to pass some values from vba to kdb+ and fetch a> >> > results real time please?> >> > I want to update kdb+ database with values by vba and get a computed> >> > result in the return.For example a vba function:> >> > kdbinterf(kdbserver,dataset,var1,var2,var3…,result)> >> > or> >> > result=kdbinterf(kdbserver,dataset,var1,var2,var3…)> >> > I think odbc/jdbc not very fast,so is there a way?>> >> > Thank you so much!>> >> > –> >> >

Submitted via Google Groups</buyatb…></jaskiewicz.ka…></buyatb…></jaskiewicz.ka…>

9

Hi BAB,

May i please request you to post the dll zip once more if you still have it? I am looking to build a macro (VBA) which will display the ticker data from KDB server.

I checked out the link you mentioned but the file seems to have been deleted.

Thanks,
Sayan