Porting SHA 256 to Excel. A question: Argument as Binary, not ASCII

Question

I am writing an a question about this code: Porting SHA-256 library to Excel

If I use the above function in Excel, I am not doing a SHA on the binary data, but in the ASCII representation of the data. As I want to "mine bitcoin in Excel" (only a didactic experiment , of course) I need to pass a binary string to this function, proper sequence of 0 and 1 bits, instead of ascii representation of 1 (4 bits).

I can do that in the excel cells or It can be a VBA code, it doesn't matter to me.

Here the excel I used:

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

The SHA256 function that I am 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 this excel function being different from the desired one. Below is a simple example in python that will illustrate it.

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 I am getting from the excel function. That is because this function is hashing that binary in the same way as hashing a string like 'hello world'.

So I need to transform the input value in this excel function from text to binary data before doing the sha round.

Link to my issue on Stackexchange: https://bitcoin.stackexchange.com/questions/90259/merkle-root-for-block-100000-calculation-in-excel

Thanks

Various CHAR()/CODE() manipulations on the argument of the function, but I failed.

Additional details on the provided StackOverflow example.

When I said I failed, I mean I googled something that returned #VALUE on the spreadsheet. I am afraid the code could have minimal positive impact. — Zippo76, Sep 09 '19 at 14:13

score 0 · Answer 1 · answered Jan 15 '20 at 15:26

a bit late (4 months after the question), but if you're still working on this: have a look at my code here: https://github.com/krijnsent/crypto_vba -> it's using the system.security.cryptography functions to get the SHA256 done.

If you download the Excel file (you only need ModHash and the right references), this bit of code gives you the right result:

Sub teststrings()

string1 = "876dd0a3ef4a2816ffd1c12ab649825a958b0ff3bb3d6f3e1250f13ddbf0148cc40297f730dd7b5a99567eb8d27b78758f607507c52292d02d4031895b52f2ff"
string2 = "10000111011011011101000010100011111011110100101000101000000101101111111111010001110000010010101010110110010010011000001001011010100101011000101100001111111100111011101100111101011011110011111000010010010100001111000100111101110110111111000000010100100011001100010000000010100101111111011100110000110111010111101101011010100110010101011001111110101110001101001001111011011110000111010110001111011000000111010100000111110001010010001010010010110100000010110101000000001100011000100101011011010100101111001011111111"

'Convert string1 to bytes
nBytes = Len(string1) \ 2
Dim a() As Byte
ReDim a(nBytes - 1)
For i = 0 To nBytes - 1
    a(i) = CDec("&H" & Mid(string1, 1 + i * 2, 2))
Next

Set SHAhasher = CreateObject("System.Security.Cryptography.SHA256Managed")
bytes = SHAhasher.ComputeHash_2(a)
TestResult = ConvToHexString(bytes)
Debug.Print TestResult
'cd93fc352d3b9f27392b3052c61190609fdc80194ade62771ce9588808980be9

TestResult = ComputeHash_C("SHA256", string2, "", "STRHEX")
Debug.Print TestResult
'c9aee68969373b4aecc87382fb2aa28276c6b9a9bfb6956615b4b29eb14d51d2

End Sub

Hope that helps

Porting SHA 256 to Excel. A question: Argument as Binary, not ASCII

1 Answers1