Volunteer for XLSXRD

[KRB]

John,

I would love to volunteer to try out any alpha of XLSX utils.

I have some serious stability issues with pywin with my particular
incantation of my XLSX sheets.

It uses the Bloomberg API along with some embedded VB code they wrote
for me.
(Bloomberg is a plugin so not sure if that's a non-starter for you).

I have a need to manipulate the data, and python is just the ticket.

My environment is:
- Vista
- python 2.6.5

Essentially the base functionality is to:
1. Open the "source" XLSX from within python (in the background) while
allowing other Excel instances on the machine to run unhindered.
2. Allow the Bloomberg API to download data.
3. Read the data, perform analysis/munging within python
4. Then write the results out to another Excel file with some basic
Excel formulae included. (SUM, AVG etc)

If XLSXRD is Read Only, that is not a show stopper as Step 4. does not
have to be XLSX and could indeed be XLS and use the standard xlutils
library.

Let me know if this would be helpful because it sounds like it sure
would be helpful to me.

[John Machin]

On 7/10/2010 3:06 AM, KRB wrote:
> John,
>
> I would love to volunteer to try out any alpha of XLSX utils.
>
> I have some serious stability issues with pywin with my particular
> incantation of my XLSX sheets.

Aside: I presume that is an abbreviated version of "issues with the
things (Windows, Excel, VB, Bloomberg API) that I'm using pywin32 to
drive" :-)

>
> It uses the Bloomberg API along with some embedded VB code they wrote
> for me.
> (Bloomberg is a plugin so not sure if that's a non-starter for you).

xlsxrd is currently a bolt-on for xlrd. When officially released, it
will be integrated into xlrd, and already in common with xlrd has the
following attributes:

pure Python
runs on Python versions 2.1 to 2.7 inclusive
runs on any CPython-supported platform i.e. not restricted to Windows
extracts data from Excel files; there is no execution engine; it does
not drive an Excel instance

>
> I have a need to manipulate the data, and python is just the ticket.
>
> My environment is:
> - Vista
> - python 2.6.5
>
> Essentially the base functionality is to:
> 1. Open the "source" XLSX from within python (in the background) while
> allowing other Excel instances on the machine to run unhindered.
> 2. Allow the Bloomberg API to download data.
> 3. Read the data, perform analysis/munging within python
> 4. Then write the results out to another Excel file with some basic
> Excel formulae included. (SUM, AVG etc)

Which parts of the above have stability issues?

>
> If XLSXRD is Read Only, that is not a show stopper as Step 4. does not
> have to be XLSX and could indeed be XLS and use the standard xlutils
> library.

If you can "save as XLS" after step 2 without losing any data, then you
don't need xlsxrd. So maybe you can manage 1 and 2 with your existing
code, save it to an XLS file, then do 3 & 4 with xlrd/xlwt/xlutils.

Scenario 2: (assumes step 3 reduces data to fit in XLS) ... save to
XLSX, then do 3 & 4 with xlsxrd/xlwt/xlutils. Note it would have to be a
kludged version of xlutils (import xlsxrd instead of xlrd) until xlsxrd
is integrated.

Cheers,
John

[KRB]

On Oct 6, 2:39 pm, John Machin <sjmac...@lexicon.net> wrote:
> On 7/10/2010 3:06 AM, KRB wrote:
>
> > John,
>
> > I would love to volunteer to try out any alpha of XLSX utils.
>
> > I have some serious stability issues with pywin with my particular
> > incantation of my XLSX sheets.
>
> Aside: I presume that is an abbreviated version of "issues with the
> things (Windows, Excel, VB, Bloomberg API) that I'm using pywin32 to
> drive" :-)

Ayup! :)

>
> > It uses the Bloomberg API along with some embedded VB code they wrote
> > for me.
> > (Bloomberg is a plugin so not sure if that's a non-starter for you).
>
> xlsxrd is currently a bolt-on for xlrd. When officially released, it
> will be integrated into xlrd, and already in common with xlrd has the
> following attributes:
>
> pure Python
> runs on Python versions 2.1 to 2.7 inclusive
> runs on any CPython-supported platform i.e. not restricted to Windows
> extracts data from Excel files; there is no execution engine; it does
> not drive an Excel instance
>
>
> > I have a need to manipulate the data, and python is just the ticket.
>
> > My environment is:
> > - Vista
> > - python 2.6.5
>
> > Essentially the base functionality is to:
> > 1. Open the "source" XLSX from within python (in the background) while
> > allowing other Excel instances on the machine to run unhindered.
> > 2. Allow the Bloomberg API to download data.
> > 3. Read the data, perform analysis/munging within python
> > 4. Then write the results out to another Excel file with some basic
> > Excel formulae included. (SUM, AVG etc)
>
> Which parts of the above have stability issues?

Well the pywin part actually. It's very unreliable in my instance. I
believe it has to do with having to call the Bloomberg XLA (otherwise
you don't actually get the Addon API from within Python, you just get
vanilla XLSX) but net-net, it's what I need to do.

>
>
> > If XLSXRD is Read Only, that is not a show stopper as Step 4. does not
> > have to be XLSX and could indeed be XLS and use the standard xlutils
> > library.
>
> If you can "save as XLS" after step 2 without losing any data, then you
> don't need xlsxrd. So maybe you can manage 1 and 2 with your existing
> code, save it to an XLS file, then do 3 & 4 with xlrd/xlwt/xlutils.

Unfortunately no. It has to be XLSX. The VB code written by Bloomberg
for me breaks in horrible horrible (shudder) horrible ways when run in
XLS.

> Scenario 2: (assumes step 3 reduces data to fit in XLS) ... save to
> XLSX, then do 3 & 4 with xlsxrd/xlwt/xlutils. Note it would have to be a
> kludged version of xlutils (import xlsxrd instead of xlrd) until xlsxrd
> is integrated.

Great! Sorry for the dullard question, but where would I get my hands
on XLSXRD ? I would be happy to try it and report back. I'll get it
working with a non-Bloomberg setup first, just to make sure I have the
install etc right, then try it with my template.

> Cheers,
> John

Cheers,
Keith.

[Dmitry]

So in step 2 you can get the Bloomberg XLA to execute without an Excel
instance running? That would be quite an achievement. I'd be thankful
for any pointers to documentation on this subject.

I do much of the same, but I go directly through
win32com.client.Dispatch("Bloomberg.Data"), thus bypassing Excel. It
is very robust, has not broken once in years.

Thanks,
DH

[KRB]

On Oct 8, 5:22 am, Dmitry <hinda...@gmail.com> wrote:
> So in step 2 you can get the Bloomberg XLA to execute without an Excel
> instance running? That would be quite an achievement. I'd be thankful
> for any pointers to documentation on this subject.

This is getting a little off topic, but you can't (obviously) run the
XLA with an instance of Excel running. I have a specific XLSX template
I run. Step1 launches that template. XLSRD etc is interesting since my
issues were with manipulating the data once the BB API had downloaded,
not actually downloading the data. In fact, using XLSRD with a
populated template without launching Excel would be useful, by the
sheer way =BDS, =BDH in Bloomberg works. The data is simply text, and
once the file is saved, its there, and no longer reliant on the API.
(So have Pywin open the XLSX template using an Excel instance,
populate the ticker, let the XLA download the data, save the file,
quit Excel, have XLSRD read the file, manipulate the data etc)

I do believe my issues are with timing in pywin. By running the same
code in visible mode, it works. Putting sleep(x) statements in some of
the more repetitive tasks and visible=0 also seems to work (until I
hit my API limit).

I still would like to use XLSRD, as it would save a lot of API calls
(as I said, the data is there, and reading the file without opening
Excel means the API won't be called, saving me redundant API calls).

> I do much of the same, but I go directly through
> win32com.client.Dispatch("Bloomberg.Data"), thus bypassing Excel. It
> is very robust, has not broken once in years.

Yes, I have both python and Java code that achieves the same thing, by
directly accessing the FLDS etc. But my issue is this template has
been setup specifically to meet my needs, and I use it as a manual
tool also, but there is a lot to be said for automating certain
functions, as well as some numpy routines I would like to run over the
data.

As I said, feel free to email me directly: alagalah - at - gmail.

Cheers

[KRB]

*without an instance of Excel running... sorry