Working on it now. I'll be posting something in a bit.

There definitely were reasons for it... just kidding Volodya :sunny:

@gap: I am not able to edit the spreadsheet you uploaded to google drive. Is it view only?

Can't you download it? I think it is too complex to be edited online :yep:

Small problem: I have just tried copying/pasting the first (and last) U-27's patrol into the google document you have shared. Unfortunately, it seems that pasting multiple cell selections is not supported, and copying cells one by one is a bit umpractical :-?

Am I missing any obvious method to do it? :hmm2:

Okay, I've downloaded it an everything makes sense. Since I'm fairly dense, I want to be absolutely clear about the process. Please let me know if the summary below is incorrect or incomplete.


In addition to general data like U-boat type and commander's name, we will have three major pieces of information to collect for each day of the patrol:

1. lat/long
2. number of ships sunk or damage
3. current activity

We will then enter that data into your original spreadsheet (the one I copied and posted as a google document). How do you want to transfer that data from the google spreadsheet into your much more attractive and more complex version?

Finally, the google docs spreadsheet contains cells for number of torpedoes and engaged ships, although you have also mentioned that you have used a statistical method to determine torpedo loadout. How do you want us to enter torpedo data?

I haven't had a problem copying and pasting multiple cells. I just tested it by highlighting a series of horizontal cells within the spreadsheet, clicking on another cell (where I want the copied horizontal series to be pasted), and pasting it there.

I also tested it with a series of numbers from an external document, and it seemed to work.

Dense?! :huh: :haha:
Indeed you are not :03:

Exact. Remember to record failed attacks that you know of with a 0 "ships sunk or damage" setting. Recording the attack in the "current activity" cell leaving the "ships sunk/damaged" blank, will be also okay. This will make the spreadsheet to generate a "failed attack" flag, and to increase the attack counter by 1.

In addition, there are 2 more inputs that you can use: "interpolate missing coordinates" will make the spreadsheet to calculate a set of intemediate coordinates between two given points (that you should mark with the tags "from this point >"/"< to this point". This is useful when some position logs are not recorded by uboat.net. Notice that this feature allows you to plot only an uninterrupted set of coordinates at a time. If you want to plot more that one set of coordinates, you should first interpolate the first set, copy the generated coordinates from the output section (first ocre row), paste them as input coordinates, and the go on with the next set of lacking coordinates. I hope I made myselfer clear here, but it is easier to do that to be explained.

The second set of inputs, "interpolate in game", is meant for marking two coordinates that shouldn't be interpolated in game (see one of my posts of yesterday for further explaination on its use). This setting will determine the 0/1 number after each coordinate, in the outputs first row. This leads me to think that I have made a mistake: the additional slash-digit after each coordinate, will make it unreadable as an input, thus making the whole 'multiple sets of lacking coordinates interpolation' process (see above) slower. I should either move this entry elsewhere, or make the spreadsheet to ignore that additional information, when reading an input coordinate. :yep:

Exact :up:

I thought of a simple copy/paste. Normally it is possible to do it from excel to excel (pasting the copied cells as values rather than as formulas), from excel to any text format and from text format to excel (as far as two consecutive columns are separated by a tabulation). Unfortunately, id doesn't seem to work from excel to google documents format :-?

This part should be coded by TDW. At the moment, my calculator spreadheet is keeping note of the torpedoes loaded aboard at the beginning of each patrol (this is what the U-boat type, mines carried and cargo onboard cells are meant for), and of the attacks performed during the patrol. When we are calling for reinforcements, if a friendly U-boat is in our area and available for supporting our attack (in conformity with the dispatch chance/dispatch range number), the next step should be checking if she got enough torpedoes. The starting torpedo loadout is taken, and for each performed attack a dice is rolled for determining how many torpedoes were spent, in a range from 0 to 4 (or whatever number we decide to use). Indeed the probability tha 0 or 4 torpedoes were used during an attack should be way lesser than 1 or 3 torpedoes, and on average, 2 torpedoes should be the rule. In order to make it to happen, we need mimic a normal distribution, and the calculations in the last two sheets of the spreadsheet I sent to you, are my (successful) attempt to do so using a quick method. :yep:

I cannot get it to work :hmm2:

This is the chart of the patrol I mentioned before

http://img12.imageshack.us/img12/2944/u27x.jpg

(green + signs = otbound passage; blue squares = patrol coordinates; yellow squares with the x sign = sunk ships; yellow triangle = enemy attack and U-boat MIA :dead:)

and this is the compact log:

can you paste it into the google document, starting from Aug 23, 1939?

Wait wait, it worked :yeah:

for some reason the google document wouldn't accept the right click/paste command, but Ctrl/V works flawlessy :doh:

I think it is better, if we work on a downloaded copy, followed by combining it all in and uploading it on the google.

That is where the break-down of who is working on which range of u-boats should help greatly.

p.s. also a question: how are we arranging it all on a spreadsheet?
Does each uboat gets its own sheet or we just make one under another?
I also don't seem to be able to see the u-boat #. (talking in regards to the link in post #49)

Why do you think so? As long as anyone knows what U-boat/patrols he should work on (for avoiding overlapping) and someone makes frequent back-up copies of the file (for avoiding unreparable mistakes), I don't see any problem about working on the same file at the same time. Of course, this working method requires that anyone adding a new log should check it for mistakes meticulously, before closing the document. :yep:

one sheet = one month. U-boats on whose patrol are overlapping on the same month are to be arranged one above another, preferably (but not necessarily) in numerical order. For making each sheet more readable, we can add an empty row beetwen two consecutive U-boats.

Yes, I didn't include U-boat numbers because they are not very recursive. Adding in the shared document three lines with the U-boat name for each month having at least one patrol log of the given boat is enough.

P.S: new log uploaded. This time it is Günter Kuhnke and the first patrol of his U-28 :know:

http://img251.imageshack.us/img251/4104/u281.jpg

(white circle = single contact detected; red dash = inbound passage)

Sorry, I should have been more explicit that I was using ctrl + v.

I see your point. I think our confusion is stemming from the fact that we are trying to keep the older spreadsheet format even while we use gap's beautiful new coordinate generator. Maybe it would be simpler to design a google spreadsheet that exactly mimics the appearance of gap's user input section, but without any of the underlying formulas. We could then enter data into the appropriate, clearly-labeled cell, and copy & paste might be more straightforward. The advantage of doing it online is that we can constantly check each other's work, both for accuracy and as a troubleshooting reference. If doing it online adds more problems than it solves, then we should think of another method. What do you guys think? :hmmm:

The sheets are currently arranged by month/year. The U-boat numbers should be displayed in the leftmost column. Each U-boat currently has two rows. The first corresponds to daily positions; the second is the "magic number."

Yes, I had forgot to mention it

If you check my updated patrols, I have added a third line, in conformity with the funky coordinates generator. What you need to do after using the generator:

1 - add one (or more) new sheet(s) to the online document, if it is the first time a patrol for the given month(s) is recorded in it, and add dates in the first row, from 1 to 31 (or 30, 28, 29).

2 - add a boat type header [SSTypeVII*] in the first column, if it is the first time a patrol for the given boat type is recorded on a certain month. By TDW's request, headers should have the same name as the cfg files in data/Roster/German/Submarine path (this makes me to think that we should add an _AI suffix at the end of the headers I have already set for type VIIA boats).

3 - under the correct header of the appropriate month(s), input three times the name of the U-boat whose patrol you are going to add. Preferably, if more U-boats of the same type are already recorded for that month(s), place the new log in U-boat name order, eventually adding new lines between two already logged boats.

4 - select the thre rows that a log generated by the calculator spreadheet is composed of, from the fist column to the last day of the first month of patrol, and paste them in the appropriate position of the first month of patrol of the shared document. Keep doing the same for each further month.

5 - fill the empty spaces (before the beginning of the patrol and after its end) with minus signs. These are place holders, telling TDW's code that at the current date the patrol for the boat recorded in the first colunmn has not started yet, or that it is already finished.

6 - months featuring no patrol by a boat, don't need to get its name recorded in the first column.

That's all, I think.

Well, I finally think I have it straight. Basically, we are using the coordinates generator to do exactly what its name suggests it does: to generate data, specifically the "magic number." Then we are using that data to populate the google docs spreadsheet. Actually, this is much simpler than I was imagining it to be since that all those seemingly complicated numbers are created for us.

For some reason, I had the process reversed. Rereading earlier posts, this all seems utterly obvious, and I'm sorry I've asked you too explain it so many times. Your explanations were spot on, but I wasn't grasping the big picture. Told you I could be dense... :doh:

Perfectly clear now. Thanks gap :yeah:

I've added sheets for all the remaining months, so this should no longer be necessary.

I just finished U-33's first patrol and have uploaded it to the google doc. We all should give gap a virtual round of applause (if not a standing ovation) for his incredible work on this generator. It is very straighforward and user-friendly, but the data it magically generates is anything but simple. I'm actually astounded by how easy it is to use.

:salute:

Before we open the floodgates and start the project for real, I think it would be best if each person does a single patrol--consider it a shakedown cruise. Then we can discuss any problems and offer suggestions for further refinement. If all goes well, I will start a new thread that will contain a concise summary of the project, step-by-step instructions for data collection, and U-boat assignments for each volunteer. I will update the assigments regularly so that you only need to glance at the first post to be reminded where to begin.


For now, let's each look at a single patrol:

• Sartoris: U-34, 1st patrol (19/8/39-26/9/39)
• V13dweller: U-34, 2nd patrol (17/10/39-12/11/39)
• volodya61: U-34, 3rd patrol (1/1/40-6/2/40)
• Targor Avelany: U-34, 4th patrol (11/3/40-30/3/40)
• keysersoze: U-34, 5th & 6th patrols (3/4/40-30/4/40, 22/6/40-18/7/40)
• gap: U-34, 7th patrol (23/7/40-3/8/40)

Is this okay with everyone? I think I included all those who expressed interest in helping, but let me know if I have excluded anybody. In the future, we will assign U-boat ranges, rather than single patrols.

Please post any suggestions or difficulties so we can create the most efficient process possible. This is exciting stuff guys! It will be a long process, but this could take SH5 to another level of dynamism, immersion, and fun.

EDIT: @gap - One suggestion I forgot to mention was perhaps allowing us to tag whether a ship was sunk by gunfire. uboat.net has a list of every merchant vessel sunk, so it is generally quite easy to determine whether it was a torpedo attack or deck gun attack. This could be very important for determining torpedo loadout for certain patrols. For example, on U-33's second patrol, she sank three small fishing vessels (all about 300 GRT) with her deck gun. I'm concerned that if I record three ships as sunk or damaged on this day, I will unnecessarily deprive Kplt. von Dresky of many of his eels.