<p>The SHA256 function that you are using in the excel sheet is hashing the binary as a text rather than using the bits from it. This results in the output of your excel function being different from the desired one. Below is a simple python script that will illustrate it to you.</p>



<pre><code>import hashlib
string1 = '876dd0a3ef4a2816ffd1c12ab649825a958b0ff3bb3d6f3e1250f13ddbf0148cc40297f730dd7b5a99567eb8d27b78758f607507c52292d02d4031895b52f2ff'
string2 = '10000111011011011101000010100011111011110100101000101000000101101111111111010001110000010010101010110110010010011000001001011010100101011000101100001111111100111011101100111101011011110011111000010010010100001111000100111101110110111111000000010100100011001100010000000010100101111111011100110000110111010111101101011010100110010101011001111110101110001101001001111011011110000111010110001111011000000111010100000111110001010010001010010010110100000010110101000000001100011000100101011011010100101111001011111111'

hashlib.sha256(bytes.fromhex(string1)).hexdigest()
&gt;&gt;&gt; 'cd93fc352d3b9f27392b3052c61190609fdc80194ade62771ce9588808980be9'

hashlib.sha256(string2.encode('utf-8')).hexdigest()
&gt;&gt;&gt; 'c9aee68969373b4aecc87382fb2aa28276c6b9a9bfb6956615b4b29eb14d51d2'
</code></pre>

<p>The first output is the actual one you should get from the first round of hashing. The second output is the one that you are getting from the excel function. That is because you are hashing that binary in the same way as hashing a string like 'hello world'.</p>

CC BY-SA 4.0
2019-09-06T17:49:37.560Z
Are you familiar enough with Excel to suggest how shall I fix that?
Zippo76
CC BY-SA 4.0
2019-09-06T19:44:10.300Z
That question is better asked on Stackoverflow.
Ugam Kamat
CC BY-SA 4.0
2019-09-07T12:42:45.253Z
I would ask this question on the thread where i found this function (this one) , but i lack the reputation needed to comment (rather than posting a "fake answer". Can you point me in the right direction to do that without pissing off mods or other useers?
Zippo76
CC BY-SA 4.0
2019-09-12T10:53:54.120Z
No luck so far.
Zippo76
CC BY-SA 4.0
2019-09-12T11:50:21.877Z
@Zippo76 can you link the post where you found this function? Also you can accept this post if you found it helpful so others know that it effectively answered the question
Ugam Kamat
CC BY-SA 4.0
2019-09-15T14:22:15.470Z
I used this link as a primary source of code/informations: https://stackoverflow.com/questions/22672564/porting-sha-256-library-to-excel
Zippo76
CC BY-SA 4.0
2019-09-06T12:11:16.827Z
2019-09-06T12:19:29.397Z
2019-09-06T12:19:29.397Z
Ugam Kamat
<p>I am stuck doing the calculations for the  infamous block 100000. I am doing those in excel. I am trying to add as much detail here of the operation I am doing and intermediate results.</p>

<pre><code>TX1: 8c14f0db3df150123e6f3dbbf30f8b955a8249b62ac1d1ff16284aefa3d06d87
TX2: fff2525b8931402dd09222c50775608f75787bd2b87e56995a7bdd30f79702c4
TX3: 6359f0868171b1d194cbee1af2f16ea598ae8fad666d9b012c8ed2b79a236ec4
TX4: e9a66845e05d5abc0ad04ec80f774a7e585c6e8db975962d069a522137b80c1d
</code></pre>

<p>I byte swap those <code>txid</code>s to little endian (text functions):</p>

<pre><code>TX1: 876dd0a3ef4a2816ffd1c12ab649825a958b0ff3bb3d6f3e1250f13ddbf0148c
TX2: c40297f730dd7b5a99567eb8d27b78758f607507c52292d02d4031895b52f2ff
TX3: c46e239ab7d28e2c019b6d66ad8fae98a56ef1f21aeecb94d1b1718186f05963
TX4: 1d0cb83721529a062d9675b98d6e5c587e4a770fc84ed00abc5a5de04568a6e9
</code></pre>

<p>I concatenate the little endian of the first two transactions: (text functions)</p>

<pre><code>tx1 + tx2 = 876dd0a3ef4a2816ffd1c12ab649825a958b0ff3bb3d6f3e1250f13ddbf0148cc40297f730dd7b5a99567eb8d27b78758f607507c52292d02d4031895b52f2ff
</code></pre>

<p>I convert this 128 character hex string into a 512 binary string: (I concatenate a series of HEX2BIN(MID(concatenated,starting chars,2),8).</p>

<pre><code>10000111011011011101000010100011111011110100101000101000000101101111111111010001110000010010101010110110010010011000001001011010100101011000101100001111111100111011101100111101011011110011111000010010010100001111000100111101110110111111000000010100100011001100010000000010100101111111011100110000110111010111101101011010100110010101011001111110101110001101001001111011011110000111010110001111011000000111010100000111110001010010001010010010110100000010110101000000001100011000100101011011010100101111001011111111
</code></pre>

<p>I do a sha256 of the above binary using fresle sha function (referenced here on SE). The result is:</p>

<pre><code>c9aee68969373b4aecc87382fb2aa28276c6b9a9bfb6956615b4b29eb14d51d2
</code></pre>

<p>I convert this 64 hex string into a 256 binary string: (I concatenate a series of HEX2BIN(MID(concatenated,starting chars,2),8)</p>

<pre><code>1100100110101110111001101000100101101001001101110011101101001010111011001100100001110011100000101111101100101010101000101000001001110110110001101011100110101001101111111011011010010101011001100001010110110100101100101001111010110001010011010101000111010010
</code></pre>

<p>I do a sha256 of the above binary using fresle sha function (referenced here on SE)</p>

<pre><code>branch1= f2d117d431bf81ee1a18c12d8c6b2bb05fe0d63b86ca5f2d43b30781fdbd6583
</code></pre>

<p>I bet that you already spotted the error by now.</p>

<p>To complete I do all the above steps for the second two transactions (tx3 &amp; tx4).</p>

<p>The result of the second sha round is: </p>

<pre><code>branch2= 910dc8ac1c1373c0a22c74f4ba04fb0e0f45232e292227015400ca549ceec4ef
</code></pre>

<p>Now I redo all the above steps for branch1 + branch2, giving me:</p>

<pre><code>eb834a22120b5195270085f999afed5c7f79351e0a8ac9f2d5b355495700cddb
</code></pre>

<p>I convert back to big endian: </p>

<pre><code>dbcd00574955b3d5f2c98a0a1e35797f5cedaf99f985002795510b12224a83eb
</code></pre>

<p>This should be the merkle root, but it's not. What am I doing wrong?</p>

<p>Here the excel I used:</p>

<p><a href="https://drive.google.com/file/d/1Qdy_PpUj4ZWLGBXCsSSBmQgTMhQITLVz" rel="nofollow noreferrer">https://drive.google.com/file/d/1Qdy_PpUj4ZWLGBXCsSSBmQgTMhQITLVz</a></p>

<p>If I check my sha(hex) with anyahsh.com i get different result, while if i check sha(binary) i get the exact same result. The strange thing I double checked the hex=>bin conversion and it is correct, so I don't understand what's going wrong. </p>

<sha256>
Merkle Root for Block 100000: calculation in Excel
Zippo76
# Merkle Root for Block 100000: calculation in Excel

(Asked by: username_0 on 2019-09-05)

I am stuck doing the calculations for the infamous block 100000. I am doing those in excel. I am trying to add as much detail here of the operation I am doing and intermediate results.

```
TX1: 8c14f0db3df150123e6f3dbbf30f8b955a8249b62ac1d1ff16284aefa3d06d87
TX2: fff2525b8931402dd09222c50775608f75787bd2b87e56995a7bdd30f79702c4
TX3: 6359f0868171b1d194cbee1af2f16ea598ae8fad666d9b012c8ed2b79a236ec4
TX4: e9a66845e05d5abc0ad04ec80f774a7e585c6e8db975962d069a522137b80c1d

```

I byte swap those `txid`s to little endian (text functions):

```
TX1: 876dd0a3ef4a2816ffd1c12ab649825a958b0ff3bb3d6f3e1250f13ddbf0148c
TX2: c40297f730dd7b5a99567eb8d27b78758f607507c52292d02d4031895b52f2ff
TX3: c46e239ab7d28e2c019b6d66ad8fae98a56ef1f21aeecb94d1b1718186f05963
TX4: 1d0cb83721529a062d9675b98d6e5c587e4a770fc84ed00abc5a5de04568a6e9

```

I concatenate the little endian of the first two transactions: (text functions)

```
tx1 + tx2 = 876dd0a3ef4a2816ffd1c12ab649825a958b0ff3bb3d6f3e1250f13ddbf0148cc40297f730dd7b5a99567eb8d27b78758f607507c52292d02d4031895b52f2ff

```

I convert this 128 character hex string into a 512 binary string: (I concatenate a series of HEX2BIN(MID(concatenated,starting chars,2),8).

```
10000111011011011101000010100011111011110100101000101000000101101111111111010001110000010010101010110110010010011000001001011010100101011000101100001111111100111011101100111101011011110011111000010010010100001111000100111101110110111111000000010100100011001100010000000010100101111111011100110000110111010111101101011010100110010101011001111110101110001101001001111011011110000111010110001111011000000111010100000111110001010010001010010010110100000010110101000000001100011000100101011011010100101111001011111111

```

I do a sha256 of the above binary using fresle sha function (referenced here on SE). The result is:

```
c9aee68969373b4aecc87382fb2aa28276c6b9a9bfb6956615b4b29eb14d51d2

```

I convert this 64 hex string into a 256 binary string: (I concatenate a series of HEX2BIN(MID(concatenated,starting chars,2),8)

```
1100100110101110111001101000100101101001001101110011101101001010111011001100100001110011100000101111101100101010101000101000001001110110110001101011100110101001101111111011011010010101011001100001010110110100101100101001111010110001010011010101000111010010

```

I do a sha256 of the above binary using fresle sha function (referenced here on SE)

```
branch1= f2d117d431bf81ee1a18c12d8c6b2bb05fe0d63b86ca5f2d43b30781fdbd6583

```

I bet that you already spotted the error by now.

To complete I do all the above steps for the second two transactions (tx3 & tx4).

The result of the second sha round is:

```
branch2= 910dc8ac1c1373c0a22c74f4ba04fb0e0f45232e292227015400ca549ceec4ef

```

Now I redo all the above steps for branch1 + branch2, giving me:

```
eb834a22120b5195270085f999afed5c7f79351e0a8ac9f2d5b355495700cddb

```

I convert back to big endian:

```
dbcd00574955b3d5f2c98a0a1e35797f5cedaf99f985002795510b12224a83eb

```

This should be the merkle root, but it's not. What am I doing wrong?

Here the excel I used:

<https://drive.google.com/file/d/1Qdy_PpUj4ZWLGBXCsSSBmQgTMhQITLVz>

If I check my sha(hex) with anyahsh.com i get different result, while if i check sha(binary) i get the exact same result. The strange thing I double checked the hex=>bin conversion and it is correct, so I don't understand what's going wrong.

**Tags:** sha256


---

## 1 answers

### Answer by username_1 on 2019-09-06. Score: 2

The SHA256 function that you are using in the excel sheet is hashing the binary as a text rather than using the bits from it. This results in the output of your excel function being different from the desired one. Below is a simple python script that will illustrate it to you.

```
import hashlib
string1 = '876dd0a3ef4a2816ffd1c12ab649825a958b0ff3bb3d6f3e1250f13ddbf0148cc40297f730dd7b5a99567eb8d27b78758f607507c52292d02d4031895b52f2ff'
string2 = '10000111011011011101000010100011111011110100101000101000000101101111111111010001110000010010101010110110010010011000001001011010100101011000101100001111111100111011101100111101011011110011111000010010010100001111000100111101110110111111000000010100100011001100010000000010100101111111011100110000110111010111101101011010100110010101011001111110101110001101001001111011011110000111010110001111011000000111010100000111110001010010001010010010110100000010110101000000001100011000100101011011010100101111001011111111'

hashlib.sha256(bytes.fromhex(string1)).hexdigest()
>>> 'cd93fc352d3b9f27392b3052c61190609fdc80194ade62771ce9588808980be9'

hashlib.sha256(string2.encode('utf-8')).hexdigest()
>>> 'c9aee68969373b4aecc87382fb2aa28276c6b9a9bfb6956615b4b29eb14d51d2'

```

The first output is the actual one you should get from the first round of hashing. The second output is the one that you are getting from the excel function. That is because you are hashing that binary in the same way as hashing a string like 'hello world'.

> **Comments:**
> **username_0**: Are you familiar enough with Excel to suggest how shall I fix that?  
> **username_1**: That question is better asked on Stackoverflow.  
> **username_0**: I would ask this question on the thread where i found this function (this one) , but i lack the reputation needed to comment (rather than posting a "fake answer". Can you point me in the right direction to do that without pissing off mods or other useers?  
> **username_0**: No luck so far.  
> **username_1**: @username_0 can you link the post where you found this function? Also you can accept this post if you found it helpful so others know that it effectively answered the question  
> **username_0**: I used this link as a primary source of code/informations: https://stackoverflow.com/questions/22672564/porting-sha-256-library-to-excel  

---

