Write data to Excel with certain format

Question

I'm currently doing this in Perl, and I'd like to find a more efficient/faster way to do it. Any advice is appreciated!

What I'm trying to do is to extract certain data from a csv/xlsx file and write them into Excel so that Bloomberg can read.

Here is an example of the csv file:

Account.Name  Source.Number  Source.Name  As.Of.Date  CUSIP.ID    Value
AR            PSF30011202    DK           3/31/2016   111165194   100.00
AR            PSF30011602    MOF          3/31/2016   11VVA0WE4   150.00
AR            PSF30014002    OZM          3/31/2016   11VVADWF3   125.00
FI            PSF30018502    FS           3/31/2016   11VVA2625   170.00
FI            PSF30018102    IP           3/31/2016   11VVAFPH2   115.00
....

What I want to have in the Excel file is that if Account.Name = AR, then:

Cell A1 =Source.Name. E.g. DK.
Cell A2 =weight of Value. E.g. the weight of DK is 0.151515 (100/660).
Cell A3 = =BDH("CUSIP.ID CUSIP","PX_LAST","01/01/2000","As.Of.Date","PER=CM"). E.g. =BDH("111165194 CUSIP","PX_LAST","01/01/2000","03/31/2016","PER=CM")
Cell D1 =MOF
Cell D2 =0.227273
Cell D3 = =BDH("11VVA0WE4 CUSIP","PX_LAST","01/01/2000","03/31/2016","PER=CM")

There are two columns in between because if DK's CUSIP is valid, then A3 and after would be the dates; B3 and after would contain monthly price from Bloomberg; C4 and after will be the log returns of monthly prices (=LN(B4/B3)).

Below is what it should look like:

is there a reason you are doing this in R? Do you have any experience with VBA? — gtwebb
I have no experience with VBA. I'm doing this in Perl and trying to find a more efficient/faster way to do this. — T-T
To get answers, I suggest you abstract from your specific case and simplify your example to the minimum. (Perhaps forming a new question to gain attention again.) I don't think anyone is willing to sit down and spend an hour to transform the data a "questionable" (from an analytics perspective?) format. From the responses to your minimal example, you should be able to get where you want later on. — lukeA

ASH ASH · Accepted Answer · 2016-05-05T16:50:16

I don't know anything about Pearl and I'm not sure what you are doing, but it looks like you are getting stock prices. Is that right. Maybe you can download what you need from Yahoo.finance and get rid of Bloomberg altogether. Take a look at the link below and see if it helps you get what you need.

http://www.financialwisdomforum.org/gummy-stuff/Yahoo-data.htm

Write data to Excel with certain format

1 Answers