Write data to Excel with certain format
<p>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!</p>

<p>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. </p>

<p>Here is an example of the csv file:</p>

<pre><code>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
....
</code></pre>

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

<ul>
<li>Cell A1 =Source.Name. E.g. DK.</li>
<li>Cell A2 =weight of Value. E.g. the weight of DK is 0.151515 (100/660).</li>
<li>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")</li>
<li>Cell D1 =MOF</li>
<li>Cell D2 =0.227273</li>
<li>Cell D3 = =BDH("11VVA0WE4 CUSIP","PX_LAST","01/01/2000","03/31/2016","PER=CM")</li>
</ul>

<p>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)).</p>

<p>Below is what it should look like:</p>

<p><a href="https://i.stack.imgur.com/tZN4b.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/tZN4b.png" alt="enter image description here"></a></p>