I am trying to implement a bits extractor for CAN bus data in Excel. I struggle with the algorithm part therefore this is mainly independent from Excel/openpyxl.

I have a table with the ID being stored in column B and the binary code per time step in column F. I want to parse through the binary numbers and extract the parts which contains the signals’ value. One ID can have

one or moresignals. Depending on the ID, I want to go through all the rows and convert the binary to decimal (as shown below). The IDs contain either 3, 4, 5, 6, 7 or 8 bytes, which is defined for each ID.

Input:

```
A B C D E F G H .. M
.. 2E .... 1010100000..
...4A ... 0101010000..
```

Output:

```
A B C D E F G H .. M
..2E .... ... 672
..4A ....... 10 16
```

**I have 3 main issues (that seem so big that I have broken down into smaller questions in another StackOverflow Question):**

**1)** Openpyxl: Search for a HEX substring in cell and append the first zero's to the BIN string

**2)** For each signal, I need to add the following in order to calculate the decimal value of that signal: the resolution, the offset: `CurrentDEC = (Dec_Value*Resolution)+Offset`

These parameters are different for each signal. I want to put the CurrentDEC value in column G, H, I (if there are three signals, otherwise only in col G if only 1 signal exists). I haven’t implemented the latter as I first want to calculate correct values before writing them in the table. The implementation of calculating the CurrentDEC with individual resolution and offset is only possible if I put the content of `extract_k_bits(inputBIN,k,p)`

into each ID’s if-clause, which creates a big mess, as you can see here:

```
if cell.value == '28A':
for cell in sheet["F"]:
inputBIN= cell.value
k=16
p=11
end = len(inputBIN) - p
start = end - k + 1
kBitSub = inputBIN[start : end+1]
print(kBitSub)
Dec_Values=int(kBitSub,2)
print(Dec_Values*0.01)
```

Do you know another, more elegant way to do it?

**3)** The extract_k_bits() function is a standard function but still when I test it for a 6/7/8 byte long binary code it is not correct. When I have a 1 byte code it is little-endian but once I use a 8 byte code it is shifted. For one signal (of a 7byte code) it was shifted +7bits but another signal (another ID, 8byte code) was shifted by -21bits. I cannot explain this to myself so I thought of playing around and manually add or subtract bits in order to use the correct bits for calculation. Do you have any idea why this is happening?

**This is the code I am working with:**

```
import openpyxl
from openpyxl import Workbook
theFile = openpyxl.load_workbook('Adapted_T013.xlsx')
allSheetNames = theFile.sheetnames
print("All sheet names {} " .format(theFile.sheetnames))
sheet = theFile.active
def convertHEXtoBIN():
max_row_var = sheet.max_row+1
for row in sheet.iter_rows(min_row=1, max_row=max_row_var,values_only = True):
for cell in sheet["E"]:
if cell.value is not None:
inputHEX = str(cell.value)
res = "{0:08b}".format(int(inputHEX,16))
sheet.cell(row=cell.row, column=6).value = res
break
def extract_k_bits(inputBIN,k,p):
end = len(inputBIN) - p
start = end - k + 1
kBitSub = inputBIN[start : end+1]
print(kBitSub)
Dec_Values=int(kBitSub,2)
for row_values in range(1,sheet.max_row+1):
for row in sheet.iter_rows(min_row=row_values, max_row=row_values):
for cell in sheet["B"]:
if cell.value =='2A':
for cell in sheet["F"]:
#Signal A
extract_k_bits(cell.value,16,0)
print(Dec_Values*0.041667)
#Signal B
extract_k_bits(cell.value,16,16)
print(Dec_Values*0.041667)
#Signal C
extract_k_bits(cell.value,16,32)
print(Dec_Values*0.01)
```