Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sundarakumar
Specialist II
Specialist II

connectivity to Bloomberg

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

1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

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.

View solution in original post

8 Replies
djsampat
Creator II
Creator II

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

sundarakumar
Specialist II
Specialist II
Author

Hi Dhruv,

Thanks for the reply.

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

multi.PNG

Thanks in advance:)

Regards,

Sundar

djsampat
Creator II
Creator II

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

sundarakumar
Specialist II
Specialist II
Author

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?


range.PNG

Thanks,

Sundar

jpenuliar
Partner - Specialist III
Partner - Specialist III

petter
Partner - Champion III
Partner - Champion III

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.

sundarakumar
Specialist II
Specialist II
Author

Thanks all

ashok1203
Creator II
Creator II

Hi,

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

Thanks,

Ashok

AAK