Exporting tables to Excel

[UCL_Will]

Is it possible to export 'master tables' e.g. all the years, parameters and sets that MAY be found in successive scenarios to Excel?

If I export tables from BE to excel to create a results template and subsequently update the tables with a constrained scenario, there may be parameters included in the constrained scenario that were missing when I created the table.

Then, my results template will not correctly report the results.

[AKanudia]

Good question... something that all serious VBE users have to deal with.

I have not found a good way to do what you suggest, partly because you are anyway limited to the scenarios that are in the system currently. You can never really make a perfect template as you can't be entirely sure what elements future scenarios might bring.

But here are some practices that keep things under control for me:

- use sets instead of elements as indexes, as far as possible.

- *sum* the widest possible range of scenarios in my tables while copying them to Excel.

- manually expand the table to include elements that did not show up (yet).

There is a log file that is written in the VEDA_BE\Logs folder. It provides useful information (sometimes).

[UCL_Will]

Hi Amit,

Thanks for the reply.  Could I suggest incorporating a new function - "export table schema" - or something similar, using the sets a user specifies to create a bare bones tables that will update in the future?

I have just had to manually copy out a 1220 row table which is deeply tedious and easily rectified with a little code.

It is quite easy to work out what the most expanded table should look like - just multiply the elements of the reporting sets together.  In my case below - 7 process sets x 10 commodity sets x 16 regions = 1220.



I guess the issue will be how to deal with the many exceptions to the rule.

[UCL_Will]

My colleague, Gabrial, noted that ensuring that the updating of formulas within a workbook is set to 'Manual' greatly increases the speed with which the VBE 'Update' function works.  Would it be possible to incorporate this into the VBE code?

[AKanudia]

about export: Lets do a gotomeeting on this at some point. As far as I can understand your requirement, there should be no tedious work involved.

about calc mode: sorry, I don't want to tamper with any user settings in Excel as far as possible, for various reasons.

[UCL_Will]

A gotomeeting would be very useful.  I'll need to sort out the techy side here - e.g. headphones and mic, but will let you know when that's sorted.

[UCL_Will]

Another thought - we're finding that VBE takes around 20-30 minutes to update a single excel workbook containing around 10-15 tables.  Is there anything we should know regarding how to speed up this process?  The most time consuming part seems to be the 'Comparing Records of [veda table]'.

Regards,

Will

[Antti-L]

Yes, I am seeing this too, and would like to get it much faster. Currently, it is considerably faster to copy the data manually from the VBE table to Excel, and process it further there. For example, I just updated a workbook with just two tables, and it took about 10 minutes, mostly "Comparing Records"! I guess I could well have generated and copied the Tables manually in about one minute, and so the automatic transfer is something like 10 times slower than using manual generation and copy, one by one.

So, I would also welcome a much faster way to transport data from multiple VBE tables into Excel.
Arranging the copied table to have fixed row categories is quite easy and fast in Excel by using formulas employing the SUMIF function, and so that would not need to be done by VEDA (as that part seems to take most of the time).

Therefore, I would suggest a new simple Table transfer feature, which would just copy all the data from a user-defined set of Tables into an Excel workbook, using the current Layout of those Tables in VBE, as defined by the user. I would think that each Table should be copied into a separate Sheet of the workbook (i.e. max. one table per sheet), whenever a Table header is found in the Sheet ("Table Name: <VBE Table name>").  In this way the transfer of data should be very fast, and the rest of the data processing could be done in Excel (which is easy to automate using the known upper-left corner position).

[UCL_Will]

Thanks for the very detailed post Antti.  It sounds like you are using the same technique as our team at UCL.  We also use a variation on the SUMIF function (actually an SUM array formula using multiple arguments to filter the records to fill tables).  This allows us to generate a swanky front-end, graphs and tables for delivering to clients, or to further process and compare between runs.

The potential problem with this technique is that if a model is updated, then manual update of the underlying formulas in the spreadsheet is sometimes required.  I'm hoping to avoid this through judicious use of the excellent sets facility in VEDA-BE.

Regards,

Will

[olexandr]

...a new simple Table transfer feature, which would just copy all the data from a user-defined set of Tables into an Excel workbook, using the current Layout of those Tables in VBE, as defined by the user. I would think that each Table should be copied into a separate Sheet of the workbook (i.e. max. one table per sheet), whenever a Table header is found in the Sheet ("Table Name: <VBE Table name>").

I think this feature is more or less available in VEDA already. It is definitely possible to select multiple tables in the Batch Mode and then export them to an excel workbook: one table per sheet with the name of the table specified as the sheet name. To do this, one needs to select Export in Batch Mode Options and Different Sheets in Export Location (both in Tools => Options => Export Options). One could then use another excel file with standard output in order to connect and retrieve the data...

Olexandr

[AKanudia]

Thanks for all the good observations. You are reaching the right conclusions... here are my views and some historical perspective:

Export to Excel was all we had for several years. The main problem was that the same table would turn up with different rows/columns in different scenarios. So, it was hard to link such tables to static graphs, which is what we want to do often.

So, we thought why not have a table with a union of rows and columns across regions/scenarios and have it updated by VEDA. Note that besides retaining the structure, it is also designed to override some choices that the VEDA cubes make. Sorting, for example. So long as you don't leave blank rows/columns within the table, you can rearrange it as you like. You can even insert rows/cols with growth rates/subtotals/shares within the tables so long as the row/col labels start with a "*". AND, you should always leave blank rows and columns on all four sides of tables that are to be updated by VEDA.

Supporting all this flexibility implies firing cell-level queries that makes update a slow process. This is the "comparing tables" step.

So, update should be used with set-based tables with a few rows... not hunderds or thousands of rows. For such needs, you should post-process exported tables with smart formulas and/or VBA.

I generally use update in batch mode - looping over scenarios and regions. This goes faster as VEDA is always working with smaller tables internally. Then, these files are pulled into a single workbook for comparative tables. I have developed a fairly general VBA routine to do this pulling together, but there are too many personal preferences hard-coded in there to make it a part of VEDA. And I tinker with the code for each new project. I intend to leave this final post-processing layer to individual users at this point.

[AKanudia]

The batch mode of update offers another powerful feature: it retains the global filters on dimensions that are not being looped on. This is very useful for making tables that sum a subset of regions, for example.

[UCL_Will]

After reading your replies, I went away and reconfigured our results templates.  Instead of a medium number of large tables, I now have around 20 very small tables (e.g. final energy by sector - so around 8 rows by 10 columns).  This updates considerably faster - in around 2 minutes compared to the 15-20 minutes before; so an order of magnitude improvement.  Given the disruption otherwise incurred when I'm locked out of Excel for more than 5 minutes (I'm an addict), this has significantly improved the work-flow.  Thank you for the insights and may I recommend that this becomes a best-practice guideline in any future manuals for VBE.

A few final questions.

Schema export - I believe that a 'table schema export' would still be useful for this feature.  I'm now making small tables based solely upon sets in VBE.  Sometimes, even if I include all the scenarios I currently have in VBE, not all the sets will end up in the rows of the table.  As an example, if I compile a table to give a summary of carbon sinks, but none of my scenarios include afforestation or deforestation, these will not appear in the results sheet, despite the relevant sets included in the table definition.

Batch update - I have had a play with this, and it seems very powerful.  However, I don't find the dialogues and interface intuitive.  If I wish to export a global summary (i.e. all regions summed) for each scenario, this only worked if I applied a global filter with all the regions selected, then chose Batch Update but did not include the regions in the filter dialogue that appears.  Confused?

I think this is a very useful feature, and looping over regions within each scenario and then aggregating the summary sheets using a simple macro, as you recommended, seems a sensible way to go.

Regards,

Will

[AKanudia]

I will think about schema export. For immediate relief, you can add the missing rows/columns manually in the template that you are updating.

If you don't make any selections on the region list that pops up in batch mode, it will sum over all regions that are in the global filter. And all regions that exist if no global filter is active on regions.

Personally, I don't see how the update feature can be useful if you are dumping the scenario name in tables... they change all the time.

[UCL_Will]

Originally posted by AKanudia
The batch mode of update offers another powerful feature: it retains the global filters on dimensions that are not being looped on. This is very useful for making tables that sum a subset of regions, for example.

Hi there,

Here's a user guide for the Batch Update I've written for our internal wiki.  Options A and B work as described.  However, I have in trying to get VBE to summarise a subset of regions despite experimenting with all the iterations I can think of.  Am I doing something incorrectly?  See option see, step 2 and 6.

To Use

There are various options for exporting scenarios.  Using the 'Batch Update' mode, it is possible to export multiple scenarios at once into the results template, or export each region of a scenario into the global results template.

a. Export Global Summary of scenario

1. Import your TIAM-UCL scenario into the new VBE database.
2. Select Tools | Update Excel File | Batch Mode
3. This brings up a dialogue box in which you select an Excel template to be replicated and updated.  Select the Excel results template.
4. A second dialogue allows you to select the scenario you wish to export.  Select a single scenario.
5. A third dialogue allows you to select individual regions.  Just click okay and do not select any regions.
6. VBE now processes the update and exports the results to a copy of the Excel results template, appending the scenario name to the template name.

b. Export scenario looping on regions

This produces one duplicate of the results template for each region (so 16 spreadsheets) with no global summary.

1. Import your TIAM-UCL scenario into the new VBE database.
2. Select Tools | Update Excel File | Batch Mode
3. This brings up a dialogue box in which you select an Excel template to be replicated and updated.  Select the Excel results template.
4. A second dialogue allows you to select the scenario you wish to export.  Select a single scenario.
5. A third dialogue allows you to select individual regions.  Click 'Select All' and then click okay.
6. VBE now processes the update and exports the results to multiple copies of the Excel results template, appending the scenario name and region name to the template filename.

c. Export region subset summary of scenario

This sums all the values for a subset of regions for a particular scenario e.g. Western Europe and UK in one results sheet.

1. Import your TIAM-UCL scenario into the new VBE database.
2. Apply a global filter to the subset of regions you wish to summarise.
3. Select Tools | Update Excel File | Batch Mode
4. This brings up a dialogue box in which you select an Excel template to be replicated and updated.  Select the Excel results template.
5. A second dialogue allows you to select the scenario you wish to export.  Select a single scenario.
6. A third dialogue allows you to select individual regions.  Just click okay and do not select any regions.
7. VBE now processes the update and exports the results to a copy of the Excel results template, appending the scenario name to the template name.