topic connectivity to Bloomberg in QlikView

connectivity to Bloomberg

sundarakumar — Mon, 24 Mar 2014 08:30:02 GMT

Hi All,

Can anyone pls help to to extract data from bloomberg.com...

I need to extract data once everymonth.. i will be extracting currency related data...

Looks like i need to use some kind of API for the connectivity...

Can any one help me with a step by step process...

Thanks in advance..

-Sundar

Re: connectivity to Bloomberg

djsampat — Fri, 05 Jun 2015 05:01:40 GMT

Hi Sundar,

You can search for any stock related sites, find the historical rates or a page that has a table that can be used to either make a table or graph and then manipulate it within Qlikview.

How to do it, get the address of the site, click on Web Files, enter the address in the Internet file and follow the wizard. You can use Enable Transformation Step to rename headers or remove non-essential lines and use a cross tab and you are done. View example below.

e.g.

QUALIFY *;

UNQUALIFY Date;

AORD:

LOAD Date,

Open,

High,

Low,

Close,

Volume,

[Adj Close*]

FROM

[https://au.finance.yahoo.com/q/hp?s=%5EAORD]

(html, codepage is 1252, embedded labels, table is @10, filters(

Remove(Row, RowCnd(CellValue, 1, StrCnd(start, '*')))

));

If this helped you, please mark as Helpful. If it solves your issue, please mark as Answer

If you are viewing this topic and found it informative, please like my comment.

Regards

Dhruv

Re: connectivity to Bloomberg

sundarakumar — Mon, 08 Jun 2015 07:35:07 GMT

Hi Dhruv,

Thanks for the reply.

How can i pull in all the data that is spread across different tabs like sown below?

Thanks in advance:)

Regards,

Sundar

Re: connectivity to Bloomberg

djsampat — Fri, 12 Jun 2015 03:22:44 GMT

Hi Sundar,

Not sure I understand your issue. Please clarify.

During the Load, each column is going to be visible to add into a Graph or Table.

This is the back end script code. Once you have executed the code, and reloaded all scripts without errors, then you add a graph and if you have added a name to your table, e.g. Prices, then you can access each field as Prices.Date or Prices.High, etc and select them as Dimensions or use them as Expressions. I initially just used AORD for All Ordinaries.

QUALIFY *;

UNQUALIFY Date;

Prices:

LOAD Date,

Open,

High,

Low,

Close,

Volume,

[Adj Close*]

FROM

[https://au.finance.yahoo.com/q/hp?s=%5EAORD]

(html, codepage is 1252, embedded labels, table is @10, filters(

Remove(Row, RowCnd(CellValue, 1, StrCnd(start, '*')))

));

If this helped you, please mark as Helpful. If it solves your issue, please mark as Answer

If you are viewing this topic and found it informative, please like my comment.

Regards

Dhruv

Re: connectivity to Bloomberg

sundarakumar — Mon, 15 Jun 2015 12:17:32 GMT

Hi Dhruv,

Thanks for the reply.

The issue is , when i execute the query that you have shared i get data form 10th march to 12th june of 2015, but there is data avaialble for the time proior to this i.e. u can click the next button and you will get data for 9th march 2015 to dec 3rd of 2014. We will be missign these histroic data, is there a possibility to loop and pull all the data that are available?

Thanks,

Sundar

Re: connectivity to Bloomberg

jpenuliar — Mon, 15 Jun 2015 12:47:25 GMT

If you look at the url for each page in the yahoo finance:

https://au.finance.yahoo.com/q/hp?s=%5EAORD&a=7&b=3&c=1984&d=5&e=15&f=2015&g=d&z=66&y=0

Next:
https://au.finance.yahoo.com/q/hp?s=%5EAORD&a=7&b=3&c=1984&d=5&e=15&f=2015&g=d&z=66&y=66

Next:
https://au.finance.yahoo.com/q/hp?s=%5EAORD&a=7&b=3&c=1984&d=5&e=15&f=2015&g=d&z=66&y=132

Last page:

https://au.finance.yahoo.com/q/hp?s=%5EAORD&a=7&b=3&c=1984&d=5&e=15&f=2015&g=d&z=66&y=7788

you will notice that "y" changes value (0,60,132...7788)

You can load the whole table from yahoo by applying a bit of logic to the url variable y

Re: connectivity to Bloomberg

petter — Mon, 15 Jun 2015 12:48:15 GMT

The last part of the URL that is behind the Next looks like this:

?s=%5EAORD&a=7&b=3&c=1984&d=5&e=15&f=2015&g=d&z=0&y=66

The Question-Mark is what is separating the location part of the URL from the parameters it sends to the receiving HOST and webpage once you click Next. Inspecting what the URL looks like when I click next I find that it looks like this

?s=%5EAORD&a=7&b=3&c=1984&d=5&e=15&f=2015&g=d&z=66&y=132

The z and the y parameters are the only changing parameters and it is quite obvious that y is an index/limit and z is the page. It returns z rows at a time ending at y-1 (or something like that - didn't test it fully myself).

So if you loop in your load script retrieving 66 rows at a time (you might be able to change the actual number) you can page through the table with a for loop until you have all your rows.

FOR page=0 to 100

index = page * 66;

LOAD

....

FROM

..... [http: <full-location>.... ?s=%5EAORD&a=7&b=3&c=1984&d=5&e=15&f=2015&g=d&z=66&y=$(index)] (....... );

NEXT;

Be aware if you employ this method you might be in breach of usage terms of the web-site unless you have a subscription or other agreement in place that allows you to do this. That is your responsibility to find out.

Re: connectivity to Bloomberg

sundarakumar — Thu, 18 Jun 2015 06:27:57 GMT

Thanks all

Re: connectivity to Bloomberg

ashok1203 — Wed, 04 Jul 2018 07:20:21 GMT

Hi,

Can you please help me to know the procedure to connect Bloomberg from Qlik directly.

Thanks,

Ashok