Smokie,
I am still here and very interested in swapping knowledge on our excel tools. The beginning of the year (actually first two months) are always a nightmare for me. Closing the books on one year and planning for the next. All while dealing with a bunch of Auditors. Fun Days!
I think I can write a formula that will identify the pivots and their locations without having to put the data into the grid format. Just paste all of the data into the template file and let it return all pivots based on the location of first character of the pivot in the string of text. It could have a drop down box to select grid width (number of Columns), or just a range of grid widths. example: Any 4 character pivots, if the text were placed in a grid from 4 to 80 columns.
If it returned any positive results, then we could input that section of the text into a grid for further analysis. What do you think?
I hope to work on it a little this week, but not sure when I will fit it in.
I would love to take a look at your raw excel files. I have dropbox and google drive.
I will shoot you PM later today to discuss.
Thanks,
I think I can write a formula that will identify the pivots and their locations without having to put the data into the grid format. Just paste all of the data into the template file and let it return all pivots based on the location of first character of the pivot in the string of text. It could have a drop down box to select grid width (number of Columns), or just a range of grid widths. example: Any 4 character pivots, if the text were placed in a grid from 4 to 80 columns.
If it returned any positive results, then we could input that section of the text into a grid for further analysis. What do you think?
Hmmmm, not exactly sure what you mean. But I think we could definitely learn from each other.
The pivot detector is here:
https://drive.google.com/drive/folders/ … sp=sharing
You might find my work raw. I am self taught and have never collaborated or worked with spreadsheets with anyone before. But it should be clean and logical I hope.
Light green shaded cells are for input ( "go" ), and light pink ( "stop" ) or other colored cells have formulas in them. There are three text "books," Jarlve’s 100 plaintext library of messages, Brave New World, and the Top 100 poems.
A book "chapter" has 96 messages, and the sheet only looks at 32 "subchapter" messages at a time. Just select what book you want, what chapter you want, and what subchapter you want.
For example, to look at the poetry, select 3 for book in cell C2, then chapter 1 and subchapter 1. Scroll down to see the pivots. Then choose subchapter 2, then 3. Then change the chapter to 2 and so on.
I wanted to show one pair pivots, like this:
.A
A
..A
A
…A
A
and two pairs like this:
..AB
AB
.AB
A
B
.A B
A
B
and three pairs
.ABC
A
B
C
Do this with 1, 2 or 3 in cell U5. I did it for testing and so maybe two pairs could be used to find if poetry makes more pivots than regular English.
You may find a lot of formulas in a lot of cells, and I try to keep formulas short as possible and use more short formulas in more cells instead of longer formulas and fewer cells. There are a lot of them, but the pivots can exist in four directions, so that makes it so that if you try to write one formula to fit all, then you will get false positives I think.
I found some really cool pivots, like this arrangement in poetry, chapter 4, subchapter 3, message 91. That’s four EDIT: three pivots all bunched together. Enjoy.
The poetry sheet has 448 messages and 126 pivots and the first 448 messages of Brave New World has 135 pivots. It doesn’t look like poetry causes more pivots, at least more 3×3 pivots.
The numbers would probably go up a little bit if the sheet didn’t break up the text into messages of 340.
EDIT: I scrolled through 448 messages each of Brave New World and Poetry.
Brave New World
14 messages with 2 pivots
1 message with 3 pivots
1 message with 4 pivots.
Poetry
10 messages with 2 pivots
2 messages with 3 pivots
Total 28 out of 896, or 3% of the messages have more than one pivot.
Actually just download the new version pivot detector 2019.2 it has a bonus sheet that shows all 32 of the 992 messages with two or more pivots so you can see them all in one place.
Some more really cool pivots.
Hi curiousben,
another way to quickly detect pivots is my tool Peek-a-boo:
I also have a tool to automatically create and filter 17×20 ciphers from large text files. For example by pivots. I posted an old version here, but it has some bugs. If you are interested, I can post a current version.
For example, I loaded a very large news archive from https://wortschatz.uni-leipzig.de/de/download and used it as a source. The text blocks showing pivots were extracted from this archive. I uploaded the results to Dropbox. All files contain four or more pivots:
https://www.dropbox.com/s/djagu3hqj0r27 … 9.zip?dl=0
Pivots wrapped at the edges of the cipher are also considered.
Smokie,
That is really cool. I downloaded your the excel file. Very neat!
It’s frustrating that I haven’t had time to work on this.
Hi curiousben,
another way to quickly detect pivots is my tool Peek-a-boo:
Pivots wrapped at the edges of the cipher are also considered.
Thanks Largo,
I will take a look. I really appreciate how helpful everyone is on this board!
Smokie,
That is really cool. I downloaded your the excel file. Very neat!
It’s frustrating that I haven’t had time to work on this.
No worries, no pressure. I did it for the fun of it because once in a while I get the urge to make a spreadsheet. I thought of a way easier and more efficient way to find the pivots, even without breaking text into chunks of 340, but this is good enough for now. I don’t think that poetry causes more pivots. If it did, then it would have shown up, even with this relatively small sample.
It looks like there is about a 3% chance for two pivots in any randomly selected chunk of 340 plaintext, so I guess it is plausible that the 340 underlying plaintext could have two pivots. But, since we have 63 symbols instead of 26 possible letters, it seems pretty improbable that the pivots would show up after homophonic substitution.
In general, you get more pivots if you substitute less cyclically. A transposition before substitution also increases the probability of pivots. I did some tests with my CipherFactory. I can post some details if desired.
I find this interesting. It seems that maybe the method of substitution would affect the transparency of the plaintext pivots. The method would affect the transparency maybe more for the symbols that are farther away from the axis or closer to the axis. Maybe perfect cycles destroys the innermost transparency more or less, or randomization allows for more transparency for the innermost symbols. The symbols that are closer together maybe affected differently than the symbols that are farther apart, depending on the encoding process. If we can find a way to preserve the pivots, and also that matches up with other cycle stats, then maybe that is the way he did it. Thanks for your work, Largo.
I just had a relatively simple idea. Tests have to show whether it proves to be true. Maybe I am completely wrong:
– Take plain texts that contain a lot of pivots. Now you determine the frequency of the letters that make up the pivots.
– I suspect that the most common letters occur here (in English: E, T, A, O, N, I, S, H).
– However, these letters have the most cipher symbols with a correctly applied homophonic substitution. This means that it is precisely these pivots that are most likely to "disappear" through substitution.
– Pivots consisting of rarely used letters should therefore be more likely to be preserved. This is because fewer symbols are used for this purpose
– On the other hand, there are of course many more pivots that consist of the most common letters. So is that going to cancel itself out?
I just wanted to throw that into the room. I don’t think I have time for that in the next few days. But that’s exactly the idea I want to try next.
I just had a relatively simple idea. Tests have to show whether it proves to be true. Maybe I am completely wrong:
– Take plain texts that contain a lot of pivots. Now you determine the frequency of the letters that make up the pivots.
– I suspect that the most common letters occur here (in English: E, T, A, O, N, I, S, H).
– However, these letters have the most cipher symbols with a correctly applied homophonic substitution. This means that it is precisely these pivots that are most likely to "disappear" through substitution.
– Pivots consisting of rarely used letters should therefore be more likely to be preserved. This is because fewer symbols are used for this purpose
– On the other hand, there are of course many more pivots that consist of the most common letters. So is that going to cancel itself out?I just wanted to throw that into the room. I don’t think I have time for that in the next few days. But that’s exactly the idea I want to try next.
I love it when someone here is thinking the same thing as me at the same time. We are starting to connect, mentally, halfway around the world. 
I have been wondering too these last couple of days if the frequency of letters in pivots is the same as the frequency of letters in a plaintext message, and how frequency and homophonic substitution affects the transparency of pivots.
One other rough idea for detection. Take a lot of plaintext messages, and count all of the letters that are highlighted in period 16, 32 and 48 unigram repeats.Then count all of the letters highlighted in period 18, 36 and 54 unigram repeats. Find the difference for each message, and plot on a graph. Find the standard deviation. Then do the same thing after homophonic substitution. If there is a big difference in the 340 ( I don’t know yet ), and the difference points to one period or the other, and the pivots are on that period, then that may help confirm if the pivots are caused by the cipher. Maybe the diffusion would make this an unreliable way to detect though.
Here is what I was talking about. On the left, all period 16, 32 and 48 unigram repeats. There are 56 cells highlighted. On the right, period 18, 36 and 54 repeats. There are 36 cells highlighted. So that is a difference of 20 cells, or 56% more on the periods that show the pivots. Is this difference enough to be able to say that the pivots are more likely caused by some type of period 16 mechanism?
Note that if I make a list of all of the left right top bottom period 19 bigrams in the message, and then make a list of all bigrams at all periods reading the message right left bottom top, and then compare the P19 list with all of the other "cross reverse" lists, period 16 has the most bigram matches. That’s where the spike is.
On the left are all of the period 19 bigrams that have matching period 16 cross reverse bigrams in the message, and on the right are all of the period 16 cross reverse bigrams that have matching period 19 bigrams. Note the strange herringbone pattern on the lower right, which is made up of period 16 cross reverse bigrams that are aligned at period 19.
And if you score bigram repeats at all periods based on symbol frequency, actually period 64 comes out a little higher than period 19. On average, with period 64, the bigram repeats have symbols that appear with lower frequency than at any other period. And 16 * 4 = 64. This is where you see the strange +T and B. formations on the lower right, in the same area as the herringbone pattern.
The poetry sheet has 448 messages and 126 pivots and the first 448 messages of Brave New World has 135 pivots. It doesn’t look like poetry causes more pivots, at least more 3×3 pivots.
The numbers would probably go up a little bit if the sheet didn’t break up the text into messages of 340.
EDIT: I scrolled through 448 messages each of Brave New World and Poetry.
Brave New World
14 messages with 2 pivots
1 message with 3 pivots
1 message with 4 pivots.Poetry
10 messages with 2 pivots
2 messages with 3 pivotsTotal 28 out of 896, or 3% of the messages have more than one pivot.
I finally got a little time to play around with text you provided.
I am coming up with different counts on the messages, but still agree that the poems do not appear to have any significance vs Brave New World.
I only looked for pivots with 3 pairs. I didn’t make any calculations for just 2 pairs.
I got the following assuming a 17 column grid:
Brave New World:
Total pivots – 171
Messages with 2 pivots – 19
Messages with 3 pivots – 5
Messages with 4 pivots – 1
Messages with 2 pivots in the same direction – 9
Poems (17 column grid):
Total Pivots – 150
Messages with 2 pivots – 16
Messages with 3 pivots – 9
Messages with 4 pivots – 1
Messages with 2 pivots in the same direction – 9
I think the reason for the differences is due to the method we used to identify the pivots. I looked at all 449 messages as one long string of plain text in a 17 column grid. I assigned the "pivot point" to the message number that you used, but if a pivots was formed using the last few rows of the previous message, I picked it up. Some of the pivots overlapped messages.
I want to sort out the letters that make up the pivots and look at how they behave when the column width changes.
I looked at both Brave New World and the Poems based on column widths from 4 column through 40 columns. The Poems averaged 178 pivots and Brave New World averaged 167. So a 17 column grid happened to be the lowest pivot count for the Poems. With a high of 219 pivots when using a 9 column grid.
Brave New World had a low of 128 pivots using a 4 column grid and a high of 197 using a 30 column grid. I have attached a screenshot and will try to add my files to Smokie’s google folder. It is interesting how the count changes. The poems went from 150 at 17 columns to 200 at 18 columns.
I am going to sort out and look at the letters that make up the pivots and how the pivot count for the individual messages change as the column width is shifted. As you would probably expect there is a high concentration of T’s, H’s and E’s in the pivots.
The message that Smokie noted earlier (message 91 of the 4th set of Poems) caught my attention also. It had 4 pivots at a 17 wide grid, but it also had the following (Grid Width / Pivots):
4/1
9/1
10/1
12/1
17/4
19/3
21/1
23/1
26/1
30/1
32/1
35/2
38/1
What are the takeaways? I don’t know …..
I agree that breaking up the text into chunks of 340 cuts some of the pivots in half, and think that the takeaway is that poetry doesn’t necessarily cause more pivots. But it was a good idea and we worked on it to find out. Check that off of the list. I am still intrigued by the 56 highlighted cells for period 16, 32 and 48 unigram repeats. I have been making some straightforward homophonic messages today with the two pivot texts, and closely approximating 63 symbols and the symbol cycling that we see in the 340. So far I have not been able to match those stats.
Nevertheless, I think this experiment affirms what we already know: The pivot pairs are unique and seem likely to reflect some aspect of the encipherment method.
I think you are right, whether actual encipherment or something else. Right now I am wondering about inscription of symbols left right up diagonally right, with some type of selection pattern, and doing this with every third row. If there is a repetitive pattern, but maybe not a message at all and the pattern has a period to it, then that could cause period 19 repeats. But how to match the left right top bottom cycle stats? Is it possible? I think that the period 16, 32 and 48 unigram repeats is something to explore. I think that there are a lot of them.
