Import Settings from Excel/Export financial Data to Excel

[smallfly]

In real life I get in touch with MS Excel very often and because I'm a fan of OTTD because its the best transport simulation out now, both topics merged in my head and now I have the idea to import OTTD settings from an excel sheet as well as exporting the yearly financial data of your company to an excel sheet.

The advantage to the current status is clear: You can easily extend the settings dialog, because you just have to tell the game in which cell of the "official ottd settings excel sheet" the setting value is placed and you can export as much financial und statistical information about your company and competitors as you like.

I just added a poll to this subject, because I want to know what you think about that. And if you think something like "MS Excel isnt OpenSource" I tell you there is also "OpenOffice Calc"

[Jans]

I´m not sure it will be better to use a excel sheet for settings than plain text like now. Why you can get mor options in excel? Maybe it will good for export something statistical but who did need? I think the bilance at the end of the year will be enough. I don´t need a report for every train and every month.

[smallfly]

I´m not sure it will be better to use a excel sheet for settings than plain text like now. Why you can get mor options in excel? Maybe it will good for export something statistical but who did need? I think the bilance at the end of the year will be enough. I don´t need a report for every train and every month.

If you export nearly everything to a excel file, the user can decide himself what is important for him and what isnt.

My personal opinion is that the financial summary that you see at the end of a year and not that expressive and much too undetailed. You have to think about the sense of financial summaries: optimizing the next year by learning of your expenditure structure. At the moment can learn nothing out of it. To learn something like

- there are too many electrified trains, build more diesel ones
- you have too many tracks for too less trains
etc.

you have to go into detail (of course that implies somebody writes a patch that i.e. considers track maintenance costs, but I think somebody will be there doing it right now

[Zuu]

You might have in-dept knowledge about the excel format and how to read/write to it. If not, let me suggest using the csv-format instead. That is what I personally use when I need to export loads of data from programs to be analyzed or for creating graphs in MS Excel/OO Calc. (OO Calc seams to have better support for XY-plots with sporadic data points)

The CSV format is very simple which makes it very easy to implement an export function for it. Between each column there is a separator which can be pretty much any character (tabs, commas, semicolons etc.). For next row in a table you output a newline character.

The drawback of using the CSV format is that you can not set column widths etc. Also both Excel and OO interpret some cell values strange.

[smallfly]

let me suggest using the csv-format instead

youre right. it isnt fun to export data by generating a completely new excel file from scratch. the csv format is much more suitable for that case. and the ones of us loving statistics can easily write a excel macro or some other app to visualize and summarize the data.

[Muxy]

Please, directly in xml format.

Then it will be possible to use it in excel, openoffice, web site with an xsl transform... and other softwares...

[smallfly]

Please, directly in xml format.

Then it will be possible to use it in excel, openoffice, web site with an xsl transform... and other softwares...

even better! thanks for your input! nice to have such a constructive forum!

[Rubidium]

Please, directly in xml format.

Ofcourse... XML so people *first* need to learn XSL before being able to import it into Excel/OOCalc.
Also for XML one has to first develop an appropriate XSD, for CSV that's not needed.

For what it's worth: I see XML as a 'buzz' world, like Web 2.0 (HTML 2.0 is way better for compatability)

[smallfly]

The advantages of XML are

1.) self-speaking data
2.) availability of lots of libraries to import/export xml data

[CommanderZ]

2.) availability of lots of libraries to import/export xml data

And you don't even need such libraries CSV, basic CSV interpretation can be done within ten lines of code in most languages.

[smallfly]

Lets have a look at both data structures:

CSV

Code: Select all

player1;color;blue
player1;money;125230
player2;color;green
player2;money;1433202
player3;color;red
player3;money;94231

CSV is easy to save and its easy to load the single strings because you just have to split the lines using the semicolon. BUT: The lines are not self-speaking. You have to know how to interpret the data!

XML

Code: Select all

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<general_player_info>
     <desription>The main data of each player is saved here</desription>
     <player>
          <id>1</id>
          <color>blue</color>
          <money>125230</money>
     </player>
     <player>
          <id>2</id>
          <color>green</color>
          <money>1433202</money>
     </player>
     <player>
          <id>3</id>
          <color>blue</color>
          <money>94231</money>
     </player>
</general_player_info>

XML is more complicated, it needs more effort to save and load BUT the data structure is easy to load (because it is easy to interpret), the structure is easy to expand, which makes patching very easy. What do you think?

[frosch]

CSV is easy to save and its easy to load the single strings because you just have to split the lines using the semicolon. BUT: The lines are not self-speaking. You have to know how to interpret the data!

How about

Code: Select all

playerid;colour;money
1;blue;125230
2;green;1433202
3;red;94231

then?

(Hint: The only point about XML is having a XSD which allows generic editors to generate valid output without knowing the actual task.)

[Rubidium]

CSV would more likely look like:

Code: Select all

player;colour;money
1;blue;125230
2;green;1433202
3;blue;94231

Which in turns out to be a perfectly sensible table in Excel/OOCalc. With the XML you first need to write code before you have any application that can read it in a sensible format; a text editor or a tree representation of the XML aren't sensible ways to read XML for most users.

Why not YAML? It has libraries and such.

Code: Select all

general-player-info:
  - player: 1
    colour: blue
    money:  125230

  - player: 2
    colour: green
    money:  1433202

  - player: 3
    colour: blue
    money:  94231

Now what's better readable (for humans), XML or YAML?

[smallfly]

The readability is not important. Important is how easy the import/export functions are to realize AND how easy you can extend the format for patches.

CSV would more likely look like:

Code: Select all

player;colour;money
1;blue;125230
2;green;1433202
3;blue;94231

With that youre right. CSV would indeed look more likely like your example.

But what do you do if you want to save train information next to player information in the same file?

In XML it would look like

Code: Select all

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<players>
     <desription>The main data of each player is saved here</desription>
     <player>
          <id>1</id>
          <color>blue</color>
          <money>125230</money>
     </player>
     <player>
          <id>2</id>
          <color>green</color>
          <money>1433202</money>
     </player>
     <player>
          <id>3</id>
          <color>blue</color>
          <money>94231</money>
     </player>
</players>
<trains>
     <desription>All trains are saved here</desription>
     <train>
          <id>1</id>
          <engine>E41</engine>
          <waggons>
              <wagon>
                   <id>w17</id>
                   <cargotype>coal</cargotype>
                   <load>74 tons</load>
              </wagon> 
              <wagon>
                   <id>w18</id>
                   <cargotype>coal</cargotype>
                   <load>13 tons</load>
              </wagon>
          </waggons>
     </train>
     <train>
          <id>2</id>
          <engine>E41</engine>
          <waggons>
              <wagon>
                   <id>w34</id>
                   <cargotype>iron</cargotype>
                   <load>24 tons</load>
              </wagon> 
              <wagon>
                   <id>w37</id>
                   <cargotype>iron</cargotype>
                   <load>54 tons</load>
              </wagon>
          </waggons>
     </train>
</trains>

How would you realize that in CSV so that the file still remains understandable and easy to extend?

If you want to expand my example with lets say the age of the trains, you just have to add the line

Code: Select all

<age>12 years</age>

and you got it. Thats all. Everybody will understand the line. The functions DONT HAVE TO be rewritten, ONLY if you want to get the age information. Otherwise the XML parser will just ignore the line. In CSV you would have to add a new column. And each line that doesnt have an age information would still need a seperator sign like ";".

[thepalm]

Whilst there is a possibility that this is a idea which may have a small amount of merit, I think that using XML is rather stupid, for example, I calculated that using your format which contains only minimal information, a single train takes up about 1kb if it has 10 wagons. So 1000 trains, which is possible and your xml document is already 1 MB, which is starting to get pretty big. Say you take records once a year for 100 game years. Now youve got 100MB of data, which is pretty big, and any sort of analysis will start to take a very long time.

Using JSON the same data takes up only about half the space, and in a csv the space is even smaller.

The comment about extra values being difficult in csv is flawed.

For example consider

Code: Select all

player;colour;money
1;blue;125230
2;green;1433202
3;blue;94231

If you want something extra, you simply add an extra column which is ignored in parser versions which do not recoginize the column header. For XML it means updating whatever XLST you are using, probably updating the schema and writing new code to query that tag from a document

I fail to see why XML is any better than CSV

[smallfly]

So 1000 trains, which is possible and your xml document is already 1 MB, which is starting to get pretty big. Say you take records once a year for 100 game years. Now youve got 100MB of data, which is pretty big, and any sort of analysis will start to take a very long time.

I mixed two things. I didnt want to track the data of all trains over years for statistics. The train example was for a savegame file. I dont find 1 MB for a typical savegame very problematic. (nor 3 or 5 MB) The advantage is: I can easily modify the savegame if I wish too (NO i dont want a discussion about modifying save games; it would be possible; thats all)

The XML file should be used to track statistical data like amount of trains, loan, etc. That wont be too much to have the file size argument against XML.

As we do not have the same opinion on the general question which format to use, we should compare the single advantages and disadvantages:

advantage CSV: smaller file size
advantage XML: easy to read and modify by humans and programs; easy to expand

[Gremnon]

I fail to see the point in reinventing the wheel.
This seems to me to be trying to replace openttd.cfg with an xml or csv based system.
Why change it? It works. It's both human and program readable as it is, it doesn't need to be changed. AFAIK there are little, if any, limits on it as it is.

[smallfly]

This seems to me to be trying to replace openttd.cfg with an xml or csv based system.

I never opened "openttd.cfg" so I want require changing it I talk about patches that export statiscal data out of running ottd games. Dont be afraid. Nobody has to change anything

[planetmaker]

advantage CSV: smaller file size
advantage XML: easy to read and modify by humans and programs; easy to expand

I would put it rather this way:

advantage CSV: small, easy to read and write by both computer and humans, easy to expand
advantage XML: unknown

[smallfly]

so we have a problem. we need a further opinion.

lets ask GOOGLE FIGHT:

http://www.googlefight.com/index.php?la ... &word2=xml

I think the answer is clear now