Data from XML feeds to sheet cells?

[AnrDaemon]

I need to feed cells with changing data (that's averaged prices) available as XML feeds. One feed per cell.
To make it more practical, imagine the following situation:
Sheet1 is the control sheet, where I have input fields and lookup formulas against Sheet2.
On Sheet2, I have a long-long list of ID's in Sheet2.A:A, Long-long list of names in Sheet2.B:B and I want to insert relevant prices in Sheet2.C:C upon clicking a button on Sheet1, that would take the looked up ID's and refresh prices for them.
What could I use for that? It's not a matter of using simple sheet formulas, it is not possible to just embed all the feeds into document as data sources - updating more than 20 thousands of feeds at the same time with every sheet recalculation will just kill the application usage.
Some hand-made macro, i'm reading guide right now, but where to start looking for network access? And if there's any way to handle well-formed XML more... natively? Instead of byte-reading to see if it's ended or not yet.

[Villeroy]

OpenOffice.org creates XML documents ...
OASIS Open Document informations
... thus XSLT is the way to go ...
[XML Filter] Create XSLT filters for import and export

[AnrDaemon]

WTS Reading Comprehension skillbook.
Or WTB more explained answer.

How can this example of loading and saving a document in different from standard format help me retrieve remote feed, prepare values from it (there are many, I'm interested in few), and paste them in the cell?
If you're going to answer "Easy!", my next question - can these XSLT filters be embedded into document? Can they be reused in MS Excel?

[Villeroy]

Filters are registered in Tools>XML Filters...
XSLT translates one XML namespace into any other. Same with Excel's new *.xlsx document format. Guess which one is free and fully documented.
There is no office suite required to convert your own valid XML into any other valid XML. No, it is not easy.
http://en.wikipedia.org/wiki/XSL_Transformations

[AnrDaemon]

It's apparent that you do not understand what I need. Or I can't understand what you're trying to say.
I do not need to convert documents from one format to another. I need to populate cell values in existing document with data from remote source.

[Villeroy]

Yes, I misinterpreted the phrase "XML feed". No, I'm cliueless since I never use such feeds, but I'm pretty shure they have nothing to do with spreadsheets.
Usually you call them by some URL and you get some info snippet in a XML stream to be displayed in a Browser. Am I right?
An XML stream needs to be parsed. Any programming language provides standard modules to parse XML. So you don't want to generate documents. You want to dump the data live into a loaded document, right?
Anyway, I'll move this thread to the programming section.

[AnrDaemon]

Well, it may be stated that way.
I want to populate certain cells of currently opened document with values available in remote XML feeds.
At best, I need Excel-compatible solution.

[Villeroy]

I could answer your question regarding the office-API if you had any structure, array, hash or something to be dumped into Calc. This is a no-brainer.
But why dumping into spreadsheets if you want to use the data in more than one application? Dump into a csv, dBase, pass it to to a database server or something.
Excel and Calc can be connected with generic data sources.
For instance, a directory of csv tables can be treated as a read-only pseudo-database. You can simply drag a table or SQL-query into a spreadsheet and refresh the resulting import range from time to time. These import ranges give you several advantages compared to self-contained spreadsheets.

[AnrDaemon]

If it were my own feeds and application, I'd better slap a PHP app together and forget about it.
But it is what it is. It's a complete Excel app that already have composed links to these feeds (which are 3rd party to both me and that app author) for every row, only what I want is to avoid manual "click link, read XML, copy&paste values into appropriate rows" every time I want to update with fresh prices.

[Villeroy]

Well, this is the main issue with thousands (millions?) of Excel apps. They are incompatible to anything else and quite often they do not even work with the next Excel version. Why switching from one vendor lock-in into another one?
Really, leave those Excel apps where they are.
MsgBox "Sorry, this application requires Microsoft Excel XP"

[AnrDaemon]

Actually, it DO WORK (although it's slow comparing to Excel) in OOo Calc.
And I want it to continue to work there. Better than in Excel, if at all possible, despite it's (Calc) "speed".
I want as many people as possible to leave the grasp of Microsoft development studio and switch to human-friendly software.

[Villeroy]

Actually, it DO WORK (although it's slow comparing to Excel) in OOo Calc.
And I want it to continue to work there. Better than in Excel, if at all possible, despite it's (Calc) "speed".
I want as many people as possible to leave the grasp of Microsoft development studio and switch to human-friendly software.

At this point you lost me completely. We are discussing how and if something could be implemented or not and now you tell me that it does work? What exactly "does work" and what is wrong with *other peoples* preference for MS development studio (you can continue working with that IDE regardless of the API of whatever office-suite).
The only clue from where I can *guess* what you are talking about is the performance issue. I *guess* that something does a foreach cell in range{cell.value=val;...;}loop. Setting the data array of a range with a single API-call usually solves most perfomance issues: http://api.openoffice.org/docs/common/r ... eData.html
All you need is to wrap your data into a nested list containing doubles and strings such as ( (a1,b1,c1,d1,e1) , (a2,b2,c2,d2,e2) , (a3,b3,c3,d3,e3) ) to fill a range object with matching dimensions (getCellRangeByPosition(0,0,4,2) for this example's A1:E3) calling XRange.setDataArray(list).
Excel can be optimized in the same way, dumping a flat VB-array with 2 dimensions into a rectangle of cells (I forgot the method name).