競艇のオッズをEXCELに並べるプログラム

# 競艇のオッズをEXCELに並べるプログラム

### 競艇HPからオッズをEXCELにダウンロードする

次の関数を実行するとEXCELシートにオッズが図のように並びます
### make_sheet(urls,s_title,f_name,new)
#### get_odds(target_url)

![odds1229.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492810/f9b48568-2ef5-951d-d56d-3a75d92b0e95.png)


次の関数を実行すると図のように結果がシートに書かれます
### make_sheet2(urls,s_title,f_name,new)
### get_result(target_url)


![result.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492810/783a49eb-a940-3c9c-94c9-f5768724fad2.png)



プログラムを掲載します
```odds_download.py
#===========================================================
#
# Kyoutei Odds data download
#             T.F.
#
# Ver.1   2025/1/15 https://teimon.jp/zutto_colab_5  test1
# Ver.2   2025/1/15 https://teimon.jp/zutto_colab_5/ test2
#
# Ver.3   2025/1/16 odds download html->odds_list->Excel
# Ver.4   2025/1/17
# Ver.5   2025/1/18 result download html->result->Excel
# Ver.6   2025/01/21  change race date
#===========================================================
#-------------------------------------------------
# import
#-------------------------------------------------
from urllib.request import urlopen
from bs4 import BeautifulSoup
import numpy as np

import openpyxl
from openpyxl.styles.borders import Border, Side
from openpyxl.styles import PatternFill
from openpyxl.styles import Font

#------------------------------------------------------
# func1
#------------------------------------------------------
def get_odds(target_url):
    #-------------------------------------------------
    # htmlを読み込む
    #-------------------------------------------------
    html_content = urlopen(target_url).read()

    #print(type(html_content))
    #print(html_content[0:100])

    #-------------------------------------------------
    # beautifulsoupに読み込む
    #-------------------------------------------------
    soup = BeautifulSoup(html_content, 'html.parser')

    #-------------------------------------------------
    # <td class=oddsPoint">6.1</td>
    #-------------------------------------------------
    odds = soup.select(".oddsPoint")

    for n in range(20):
        # print(odds[n])
        pass

    #-------------------------------------------------
    # <td class=oddsPoint">6.1</td>
    # textを使うことでタグで囲まれた要素のみを抜き出せる
    #-------------------------------------------------
    text_list = list(map(lambda x: x.text, odds))

    #print(text_list)

    #-------------------------------
    # 欠場除去
    #-------------------------------
    for i in range(20):
        if text_list[i] == "欠場":
            text_list[i]="100"

    #-------------------------------------------------
    # oddsは小数値なのでflot型へキャスト
    #-------------------------------------------------
    float_list = list(map(lambda x: float(x), text_list))

    #print(float_list)

    #-------------------------------------------------------
    # numpy配列に変換
    #-------------------------------------------------------
    odds_list = np.array(float_list)

    #print(f"odds_list={odds_list}")
    #print(f"odds_list_type={type(odds_list)}")

    #-------------------------------------------------------
    # 連複種別　ticket
    #-------------------------------------------------------
    renpuk_list=np.array([123,124,125,126,134,234,135,235,136,236,145,245,345,146,246,346,156,256,356,456])

    #print(f"renpuk_list={renpuk_list}")

    #-------------------------------------------------------
    # 行列(2行20列)に合成する
    #-------------------------------------------------------
    add_list = np.append(renpuk_list,odds_list,axis=0)
    odds_matrix = np.reshape(add_list,(2,20))

    #print(f"odds_matrix={odds_matrix}")

    #-------------------------------------------------------
    # 行列を、np.sort()のように各行や各列をそれぞれソートするのではなく
    # 任意の行または列を基準にソートする場合は、np.argsort()を使う
    #-------------------------------------------------------
    argsort_list = np.argsort(odds_matrix[1,:])

    #print(f"num={argsort_list}")
    #-------------------------------------------------------
    # odds sort matrix の作成
    #-------------------------------------------------------
    odds_sort_matrix = odds_matrix[:,argsort_list]

    ticket_sort_list = odds_sort_matrix[0,:]  # 1行
    odds_sort_list   = odds_sort_matrix[1,:]  # 2行　　　　　

    #print(f"odds_sort_matrix={odds_sort_matrix}")
    #print(f"odds_sort_list={odds_sort_list}")

    #-------------------------------------------------------
    # odds<20 listの作成
    # np.where()の引数無しは、条件を満たす要素のインデックス(位置)が返される
    # 各次元(行、列など)の条件を満たすインデックスを表すndarrayのタプルとなる
    #-------------------------------------------------------
    odds_low_list = np.where(odds_sort_list<20)

    #-------------------------------------------------------
    # tupleからnp配列に変換
    #-------------------------------------------------------
    odds_low_list = np.array(odds_low_list) # (1,num)

    #-------------------------------------------------------
    # 2次元配列を1次元配列に変換
    #-------------------------------------------------------
    odds_low_list = odds_low_list.flatten() # (1,num) -> (num,)

    #print(f"odds_low_list={odds_low_list}")

    #-------------------------------------------------------
    # odds_low_listの要素数
    #-------------------------------------------------------
    low_num  = np.shape(odds_low_list)    # (num,)

    #print(f"low_num={low_num}")
    #-------------------------------------------------------
    # odds_low_matrixの生成
    #-------------------------------------------------------
    odds_low_matrix = odds_sort_matrix[:,odds_low_list]

    #print(f"odds_low_matrix={odds_low_matrix}")

    #-------------------------------------------------------
    # money, num ,ticket , odds
    #-------------------------------------------------------
    num    = low_num[0]
    ticket = odds_low_matrix[0,:]
    odds   = odds_low_matrix[1,:]
    money  = 100*odds

    #print(f"money={money} , num={num} , ticket={ticket} , odds={odds}")

    return num ,ticket , odds , money

#----------------------------------------------------------
# func2
#----------------------------------------------------------
def make_sheet(urls,s_title,f_name,new):
    global wb,ws

    #----------------------------------------------------
    # ワークブックの新規作成と保存
    #----------------------------------------------------
    if new==True:
        wb = openpyxl.Workbook()
        wb.save(f_name)
    #----------------------------------------------------
    # Excelファイルの読み込み
    #----------------------------------------------------
    wb = openpyxl.load_workbook(f_name)
    #----------------------------------------------------
    # workbook作成時に「Sheet」シートが作成されているので、それを参照
    #----------------------------------------------------
    #ws = wb[s_name]
    ws = wb.create_sheet(title=s_title)
    #ws.title = s_title # sheet名変更

    print(s_title)
    #-----------------------------------------------
    # get odds
    #-----------------------------------------------
    for i in range(12):

        #------------------------------------------
        # odds ticket
        #------------------------------------------
        num ,ticket , odds , money= get_odds(urls[i])

        print(f"Race {i+1} = {num}組 {ticket} {money}")

        #------------------------------------------
        # Race No
        #------------------------------------------
        ws.cell(3*i+3,1).value = i+1
        ws.cell(3*i+3,2).value = num
        #------------------------------------------
        for j in range(num):
            ws.cell(3*i+3,j+4).value = ticket[j]
            ws.cell(3*i+4,j+4).value = money[j]

    #-----------------------------------------------
    # set font
    #-----------------------------------------------
    font = Font(name='メイリオ',size=14)
    # write in sheet
    for row in ws:
        for cell in row:
            ws[cell.coordinate].font = font


    wb.save(f_name)

#------------------------------------------------------
# func3
#------------------------------------------------------
def get_result(target_url):
    #-------------------------------------------------
    # htmlを読み込む
    #-------------------------------------------------
    html_content = urlopen(target_url).read()

    #print(type(html_content))
    #print(html_content[0:100])
    #print(html_content)

    #-------------------------------------------------
    # beautifulsoupに読み込む
    #-------------------------------------------------
    soup = BeautifulSoup(html_content, 'html.parser')

    #-------------------------------------------------
    # <td class=oddsPoint">6.1</td>
    #-------------------------------------------------
    # <div class="numberSet1">
    #    <div class="numberSet1_row">
    #        <span class="numberSet1_number is-type4">4</span>
    #        <span class="numberSet1_text">-</span>
    #        <span class="numberSet1_number is-type3">3</span>
    #        <span class="numberSet1_text">-</span>
    #        <span class="numberSet1_number is-type2">2</span>
    #    </div>
    # </div
    #-------------------------------------------------
    #odds = soup.select(".oddsPoint")
    odds = soup.select(".numberSet1_number")


    #-------------------------------------------------
    # get_text()の引数にstrip=Trueを指定すると
    # テキストに含まれる改行や空白文字を除去してくれる
    #-------------------------------------------------
    # print([t.get_text(strip=True) for t in odds])
    #-------------------------------------------------
    # text_list = []
    # for t in odds:
    #    text_list.append(t.get_text(strip=True))
    #-------------------------------------------------
    text_list = list(map(lambda x: x.get_text(strip=True), odds))

    #print(f"text_list={text_list}")
    #-------------------------------------------------
    # resultはテキストなのでint型へキャスト
    #-------------------------------------------------
    int_list = list(map(lambda x: int(x), text_list))

    #print(f"int_list={int_list}")

    #-------------------------------------------------------
    # 3連複の表記に変える
    #-------------------------------------------------------
    int_list2=[0]*60
    for i in range(0,60,5):
        int_list2[i:i+3]   = sorted(int_list[i:i+3])

    #print(f"int_list2={int_list2}")

    #-------------------------------------------------------
    text_list = list(map(lambda x: str(x), int_list2))

    #-------------------------------------------------------
    # 3連複の結果の配列を生成
    #-------------------------------------------------------
    result_text=[]
    for i in range(0,60,5):
        result_text.append(text_list[i]+text_list[i+1]+text_list[i+2])

    #print(f"result_text={result_text}")

    #-------------------------------------------------
    # resultはテキストなのでint型へキャスト
    #-------------------------------------------------
    result = list(map(lambda x: int(x), result_text))

    print(f"result={result}")

    return result

#----------------------------------------------------------
# func4
#----------------------------------------------------------
def make_sheet2(urls,s_title,f_name,new):
    global wb,ws

    #----------------------------------------------------
    # ワークブックの新規作成と保存
    #----------------------------------------------------
    if new==True:
        wb = openpyxl.Workbook()
        wb.save(f_name)
    #----------------------------------------------------
    # Excelファイルの読み込み
    #----------------------------------------------------
    wb = openpyxl.load_workbook(f_name)
    #----------------------------------------------------
    # workbook作成時に「Sheet」シートが作成されているので、それを参照
    #----------------------------------------------------
    #ws = wb[s_name]
    ws = wb.create_sheet(title=s_title)
    #ws.title = s_title # sheet名変更

    print(s_title)
    #-----------------------------------------------
    # write result
    #-----------------------------------------------
    j=0
    for url in urls:

        result = get_result(url)

        for i in range(12):
            #------------------------------------------
            # Race No
            #------------------------------------------
            #ws.cell(2,2+j).value = j+1
            ws.cell(2,2+j).value = url[-8:]
            ws.cell(i+3,1).value = str(i+1)+"R"
            #------------------------------------------
            ws.cell(i+3,2+j).value = result[i]

        j = j+1


        #-----------------------------------------------
        # cell width
        #--------------------------------------------
        ws.column_dimensions["A"].width = 14
        ws.column_dimensions["B"].width = 14
        ws.column_dimensions["C"].width = 14
        ws.column_dimensions["D"].width = 14
        ws.column_dimensions["E"].width = 14
        ws.column_dimensions["F"].width = 14
        ws.column_dimensions["G"].width = 14

        #-----------------------------------------------
        # set font
        #-----------------------------------------------
        font = Font(name='メイリオ',size=14)
        # write in sheet
        for row in ws:
            for cell in row:
                ws[cell.coordinate].font = font


    wb.save(f_name)

#----------------------------------------------------------------------------------
# date name
#----------------------------------------------------------------------------------
date1="20241229"
date2="20241230"
date3="20241231"
date4="20250101"
date5="20250102"
date6="20250103"

#----------------------------------------------------------------------------------
# urls
#----------------------------------------------------------------------------------
'''---------------------------------------------------------------------------------
# 2020年5月11日の戸田競艇場第12レースの三連単オッズ表
# odds3t?=三連単オッズ表 rno=12レース jcd=02=戸田競艇場 hd=2020年5月11日
# odds3t?rno=12&jcd=02&hd=20200511
#-----------------------------------------------------------------------------------
  target_url='https://www.boatrace.jp/owpc/pc/race/odds3t?rno=12&jcd=02&hd=20200511'
#-----------------------------------------------------------------------------------
# 2025年1月15日の芦屋競艇場第1レースの三連復オッズ表
# odds3f?=三連単オッズ表 rno=1レース jcd=21=芦屋競艇場 hd=2025年1月15日
# odds3f?rno=1&jcd=21&hd=20250115
#--------------------------------------------------------------------------------'''
url1_1="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=1&jcd=21&hd="+date1
url1_2="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=2&jcd=21&hd="+date1
url1_3="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=3&jcd=21&hd="+date1
url1_4="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=4&jcd=21&hd="+date1
url1_5="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=5&jcd=21&hd="+date1
url1_6="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=6&jcd=21&hd="+date1
url1_7="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=7&jcd=21&hd="+date1
url1_8="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=8&jcd=21&hd="+date1
url1_9="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=9&jcd=21&hd="+date1
url1_10="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=10&jcd=21&hd="+date1
url1_11="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=11&jcd=21&hd="+date1
url1_12="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=12&jcd=21&hd="+date1
#-------------------------------------------------------------------------------
urls1=(url1_1,url1_2,url1_3,url1_4,url1_5,url1_6,url1_7,url1_8,url1_9,url1_10,url1_11,url1_12)

#--------------------------------------------------------------------------------
url2_1="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=1&jcd=21&hd="+date2
url2_2="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=2&jcd=21&hd="+date2
url2_3="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=3&jcd=21&hd="+date2
url2_4="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=4&jcd=21&hd="+date2
url2_5="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=5&jcd=21&hd="+date2
url2_6="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=6&jcd=21&hd="+date2
url2_7="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=7&jcd=21&hd="+date2
url2_8="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=8&jcd=21&hd="+date2
url2_9="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=9&jcd=21&hd="+date2
url2_10="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=10&jcd=21&hd="+date2
url2_11="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=11&jcd=21&hd="+date2
url2_12="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=12&jcd=21&hd="+date2
#-------------------------------------------------------------------------------
urls2=(url2_1,url2_2,url2_3,url2_4,url2_5,url2_6,url2_7,url2_8,url2_9,url2_10,url2_11,url2_12)

#--------------------------------------------------------------------------------
url3_1="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=1&jcd=21&hd="+date3
url3_2="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=2&jcd=21&hd="+date3
url3_3="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=3&jcd=21&hd="+date3
url3_4="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=4&jcd=21&hd="+date3
url3_5="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=5&jcd=21&hd="+date3
url3_6="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=6&jcd=21&hd="+date3
url3_7="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=7&jcd=21&hd="+date3
url3_8="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=8&jcd=21&hd="+date3
url3_9="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=9&jcd=21&hd="+date3
url3_10="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=10&jcd=21&hd="+date3
url3_11="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=11&jcd=21&hd="+date3
url3_12="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=12&jcd=21&hd="+date3
#-------------------------------------------------------------------------------
urls3=(url3_1,url3_2,url3_3,url3_4,url3_5,url3_6,url3_7,url3_8,url3_9,url3_10,url3_11,url3_12)

#--------------------------------------------------------------------------------
url4_1="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=1&jcd=21&hd="+date4
url4_2="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=2&jcd=21&hd="+date4
url4_3="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=3&jcd=21&hd="+date4
url4_4="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=4&jcd=21&hd="+date4
url4_5="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=5&jcd=21&hd="+date4
url4_6="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=6&jcd=21&hd="+date4
url4_7="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=7&jcd=21&hd="+date4
url4_8="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=8&jcd=21&hd="+date4
url4_9="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=9&jcd=21&hd="+date4
url4_10="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=10&jcd=21&hd="+date4
url4_11="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=11&jcd=21&hd="+date4
url4_12="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=12&jcd=21&hd="+date4
#-------------------------------------------------------------------------------
urls4=(url4_1,url4_2,url4_3,url4_4,url4_5,url4_6,url4_7,url4_8,url4_9,url4_10,url4_11,url4_12)

#--------------------------------------------------------------------------------
url5_1="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=1&jcd=21&hd="+date5
url5_2="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=2&jcd=21&hd="+date5
url5_3="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=3&jcd=21&hd="+date5
url5_4="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=4&jcd=21&hd="+date5
url5_5="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=5&jcd=21&hd="+date5
url5_6="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=6&jcd=21&hd="+date5
url5_7="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=7&jcd=21&hd="+date5
url5_8="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=8&jcd=21&hd="+date5
url5_9="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=9&jcd=21&hd="+date5
url5_10="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=10&jcd=21&hd="+date5
url5_11="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=11&jcd=21&hd="+date5
url5_12="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=12&jcd=21&hd="+date5
#-------------------------------------------------------------------------------
urls5=(url5_1,url5_2,url5_3,url5_4,url5_5,url5_6,url5_7,url5_8,url5_9,url5_10,url5_11,url5_12)

#--------------------------------------------------------------------------------
url6_1="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=1&jcd=21&hd="+date6
url6_2="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=2&jcd=21&hd="+date6
url6_3="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=3&jcd=21&hd="+date6
url6_4="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=4&jcd=21&hd="+date6
url6_5="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=5&jcd=21&hd="+date6
url6_6="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=6&jcd=21&hd="+date6
url6_7="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=7&jcd=21&hd="+date6
url6_8="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=8&jcd=21&hd="+date6
url6_9="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=9&jcd=21&hd="+date6
url6_10="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=10&jcd=21&hd="+date6
url6_11="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=11&jcd=21&hd="+date6
url6_12="https://www.boatrace.jp/owpc/pc/race/odds3f?rno=12&jcd=21&hd="+date6
#-------------------------------------------------------------------------------
urls6=(url6_1,url6_2,url6_3,url6_4,url6_5,url6_6,url6_7,url6_8,url6_9,url6_10,url6_11,url6_12)



#----------------------------------------------------------
# get result
#----------------------------------------------------------
url1_r="https://www.boatrace.jp/owpc/pc/race/resultlist?jcd=21&hd="+date1
url2_r="https://www.boatrace.jp/owpc/pc/race/resultlist?jcd=21&hd="+date2
url3_r="https://www.boatrace.jp/owpc/pc/race/resultlist?jcd=21&hd="+date3
url4_r="https://www.boatrace.jp/owpc/pc/race/resultlist?jcd=21&hd="+date4
url5_r="https://www.boatrace.jp/owpc/pc/race/resultlist?jcd=21&hd="+date5
url6_r="https://www.boatrace.jp/owpc/pc/race/resultlist?jcd=21&hd="+date6
#-------------------------------------------------------------------------------
urls7=(url1_r,url2_r,url3_r,url4_r,url5_r,url6_r)

#-------------------------------------------------------
# make sheet
#-------------------------------------------------------
f_name = "boart2.xlsx"
make_sheet(urls1,date1,f_name,new=True)
make_sheet(urls2,date2,f_name,new=False)
make_sheet(urls3,date3,f_name,new=False)
make_sheet(urls4,date4,f_name,new=False)
make_sheet(urls5,date5,f_name,new=False)
make_sheet(urls6,date6,f_name,new=False)

#-------------------------------------------------------
make_sheet2(urls7,"result",f_name,new=False)

#-------------------------------------------------------
print("finish")

```

## EXCELでオッズ解析を行う

プログラムを掲載します
```make_Table.py
#===========================================================
#
# Kyoutei Odds data Simuration
#             T.F.
#
# Ver.1   2025/1/18  Excel operation
#
#===========================================================
#-------------------------------------------------
# import
#-------------------------------------------------
import openpyxl
from openpyxl.styles.borders import Border, Side
from openpyxl.styles import PatternFill
from openpyxl.styles import Font

import time
#-------------------------------------------------
# simuration
#-------------------------------------------------
#----------------------------------------------------
# Excelファイルの読み込み
#----------------------------------------------------
f_name = "boat11.xlsx"

wb = openpyxl.load_workbook(f_name, data_only=False)

#----------------------------------------------------
# sheet 操作
#----------------------------------------------------
ws_list = wb.worksheets
ws_name = wb.sheetnames

#print(ws_list)
#print(ws_name)

#---------------------------------------------
# sheets
#---------------------------------------------
ws1 = wb[ws_name[1]]
ws2 = wb[ws_name[2]]
ws3 = wb[ws_name[3]]
ws4 = wb[ws_name[4]]
ws5 = wb[ws_name[5]]
ws6 = wb[ws_name[6]]
wsR = wb[ws_name[7]]
wsn = (ws1,ws2,ws3,ws4,ws5,ws6) #sheets

#---------------------------------------------
# 各シートに結果を記入
#---------------------------------------------
for i in range(12):
    ws1.cell(3+3*i, 3).value = wsR.cell(3+i, 2).value
    ws2.cell(3+3*i, 3).value = wsR.cell(3+i, 3).value
    ws3.cell(3+3*i, 3).value = wsR.cell(3+i, 4).value
    ws4.cell(3+3*i, 3).value = wsR.cell(3+i, 5).value
    ws5.cell(3+3*i, 3).value = wsR.cell(3+i, 6).value
    ws6.cell(3+3*i, 3).value = wsR.cell(3+i, 7).value

#======================================================================
# border line
#======================================================================
theSide=Side(color='000000',style='thin')
theBorder=Border(left=theSide,right=theSide,top=theSide,bottom=theSide)

#----------------------------------------------------------------------
# sheetsに罫線を描く
#----------------------------------------------------------------------
for t in wsn:
    for k in range(12):
        n = t.cell(3+3*k,2).value
        for i in range(n):
            cell_pos=i+4
            for j in range(2):
                row_pos=j+3+3*k
                val_cell=t.cell(column=cell_pos,row=row_pos)
                val_cell.border=theBorder

#----------------------------------------------------------------------
# sheetRに罫線を描く
#----------------------------------------------------------------------
for i in range(7):
    cell_pos=i+1
    for j in range(13):
        row_pos=j+2
        val_cell=wsR.cell(column=cell_pos,row=row_pos)
        val_cell.border=theBorder

#======================================================================
# h_fill=PatternFill(patternType='solid',fgColor='0000ff')
#======================================================================
h_fill=PatternFill(patternType='solid',fgColor='ffff00') # yellow

#----------------------------------------------------------------------
# calculate resalt same cells sum  with each sheets
#----------------------------------------------------------------------
x=0
for t in wsn:
    sum2=0;sum3=0;sum4=0;sum5=0;sum6=0

    for k in range(12):
        n = t.cell(3+3*k,2).value
        r = t.cell(3+3*k,3).value

        for i in range(n):
            val_cell=t.cell(3+3*k,i+4).value

            if val_cell==r:
                t.cell(3+3*k,i+4).fill=h_fill # draw result same cell to yellow color

                if i<2:
                    t.cell(5+3*k,5).value = t.cell(4+3*k,i+4).value
                if i<3:
                    t.cell(5+3*k,6).value = t.cell(4+3*k,i+4).value
                if i<4:
                    t.cell(5+3*k,7).value = t.cell(4+3*k,i+4).value
                if i<5:
                    t.cell(5+3*k,8).value = t.cell(4+3*k,i+4).value
                if i<6:
                    t.cell(5+3*k,9).value = t.cell(4+3*k,i+4).value

    #-------------------------------------
    # SUM
    #-------------------------------------
    for i in range(12):
        if t.cell(5+i*3,5).value != None:
            sum2 = sum2 + float(t.cell(5+i*3,5).value)

        if t.cell(5+i*3,6).value != None:
            sum3 = sum3 + float(t.cell(5+i*3,6).value)

        if t.cell(5+i*3,7).value != None:
            sum4 = sum4 + float(t.cell(5+i*3,7).value)

        if t.cell(5+i*3,8).value != None:
            sum5 = sum5 + float(t.cell(5+i*3,8).value)

        if t.cell(5+i*3,9).value != None:
            sum6 = sum6 + float(t.cell(5+i*3,9).value)

    #print(f"sum2={sum2}")

    #-------------------------------------
    # Lbel
    #--------------------------------------------
    t.cell(39,5).value = "2位買"
    t.cell(39,6).value = "3位買"
    t.cell(39,7).value = "4位買"
    t.cell(39,8).value = "5位買"
    t.cell(39,9).value = "6位買"

    #-------------------------------------
    # SUM
    #-------------------------------------
    t.cell(40,5).value =sum2
    t.cell(40,6).value =sum3
    t.cell(40,7).value =sum4
    t.cell(40,8).value =sum5
    t.cell(40,9).value =sum6

    #-------------------------------------
    # 購入費
    #-------------------------------------
    t.cell(41,5).value =2400
    t.cell(41,6).value =3600
    t.cell(41,7).value =4800
    t.cell(41,8).value =6000
    t.cell(41,9).value =7200

    #-------------------------------------
    # 収支
    #-------------------------------------
    t.cell(42,5).value = sum2-2400
    t.cell(42,6).value = sum3-3600
    t.cell(42,7).value = sum4-4800
    t.cell(42,8).value = sum5-6000
    t.cell(42,9).value = sum6-7200

    #-----------------------------------------------
    # boarder line
    #-----------------------------------------------
    for i in range(5):
        cell_pos=i+5
        for j in range(3):
            row_pos=j+40
            val_cell=t.cell(column=cell_pos,row=row_pos)
            val_cell.border=theBorder

    #-----------------------------------------------
    # set font
    #-----------------------------------------------
    font = Font(name='メイリオ',size=14)
    # write in sheet
    for row in t:
        for cell in row:
            t[cell.coordinate].font = font



#---------------------------------------------
# make table in sheet "result"
#---------------------------------------------
x=0
#--------------------------------------------
wsR.column_dimensions["A"].width = 14
wsR.column_dimensions["B"].width = 14
wsR.column_dimensions["C"].width = 14
wsR.column_dimensions["D"].width = 14
wsR.column_dimensions["E"].width = 14
wsR.column_dimensions["F"].width = 14
wsR.column_dimensions["G"].width = 14

#--------------------------------------------
# Table
#--------------------------------------------
for t in wsn:

    #-------------------------------------
    # 収支
    #-------------------------------------
    wsR.cell(20+x,2).value = t.cell(42,5).value
    wsR.cell(20+x,3).value = t.cell(42,6).value
    wsR.cell(20+x,4).value = t.cell(42,7).value
    wsR.cell(20+x,5).value = t.cell(42,8).value
    wsR.cell(20+x,6).value = t.cell(42,9).value

    #print(t.cell(42,5).value)
    #print(wsR.cell(20+x,2).value)
    #-----------------------------------------------
    wsR.cell(20+x,2).border=theBorder
    wsR.cell(20+x,3).border=theBorder
    wsR.cell(20+x,4).border=theBorder
    wsR.cell(20+x,5).border=theBorder
    wsR.cell(20+x,6).border=theBorder

    #-----------------------------------------------
    x=x+1

#--------------------------------------------
# 横列ラベル
#--------------------------------------------
wsR.cell(19,2).value = "2位まで買"
wsR.cell(19,3).value = "3位まで買"
wsR.cell(19,4).value = "4位まで買"
wsR.cell(19,5).value = "5位まで買"
wsR.cell(19,6).value = "6位まで買"

#-------------------------------------
# 縦列ラベル
#--------------------------------------------
wsR.cell(20,1).value = "1日"
wsR.cell(21,1).value = "2日"
wsR.cell(22,1).value = "3日"
wsR.cell(23,1).value = "4日"
wsR.cell(24,1).value = "5日"
wsR.cell(25,1).value = "6日"
wsR.cell(26,1).value = "合計"

#-----------------------------------------------
# 利益ありのセルを黄色に塗る
#-----------------------------------------------
for x in range(6):
    if float(wsR.cell(20+x,2).value) > 0 : wsR.cell(20+x,2).fill=h_fill
    if float(wsR.cell(20+x,3).value) > 0 : wsR.cell(20+x,3).fill=h_fill
    if float(wsR.cell(20+x,4).value) > 0 : wsR.cell(20+x,4).fill=h_fill
    if float(wsR.cell(20+x,5).value) > 0 : wsR.cell(20+x,5).fill=h_fill
    if float(wsR.cell(20+x,6).value) > 0 : wsR.cell(20+x,6).fill=h_fill

#--------------------------------------------
# 6日間の収支の総和
#--------------------------------------------
wsR.cell(26,2).value = "=SUM(B20:B25)"
wsR.cell(26,3).value = "=SUM(C20:C25)"
wsR.cell(26,4).value = "=SUM(D20:D25)"
wsR.cell(26,5).value = "=SUM(E20:E25)"
wsR.cell(26,6).value = "=SUM(F20:F25)"

#-----------------------------------------------
# set font
#-----------------------------------------------
font = Font(name='メイリオ',size=14)

sheet_range=wsR['A1':'H30']

for row in sheet_range:
    for cell in row:
        cell.font=font

#----------------------------------------------------
# sheet save
#----------------------------------------------------
wb.save(f_name)
```


## グラフを描く

プログラムを掲載します

```make_Graph.py
#===========================================================
#
# Kyoutei Odds data Simuration
#             T.F.
#
# Ver.1   2025/1/23 draw graph
#
#===========================================================
#-------------------------------------------------
# import
#-------------------------------------------------
import openpyxl
from openpyxl.styles.borders import Border, Side
from openpyxl.styles import PatternFill
from openpyxl.styles import Font

import openpyxl.chart
import openpyxl.chart.series

import time
#-------------------------------------------------
# simuration
#-------------------------------------------------
#----------------------------------------------------
# Excelファイルの読み込み
#----------------------------------------------------
f_name = "boat11.xlsx"
f_name2 = "boat12.xlsx"

#wb = openpyxl.load_workbook(f_name, data_only=True)
wb = openpyxl.load_workbook(f_name, data_only=False)

#=============================================
# Table
#=============================================
#----------------------------------------------------
# sheet 操作
#----------------------------------------------------
ws_name = wb.sheetnames

#---------------------------------------------
ws1 = wb[ws_name[1]]
ws2 = wb[ws_name[2]]
ws3 = wb[ws_name[3]]
ws4 = wb[ws_name[4]]
ws5 = wb[ws_name[5]]
ws6 = wb[ws_name[6]]

ws = wb[ws_name[7]] #result sheet

#---------------------------------------------
# make graph
#---------------------------------------------
#---------------------------------------------
# scatter chartの作成
#---------------------------------------------
chart = openpyxl.chart.ScatterChart()

chart.title="3連複購入収支"
chart.style="10"

chart.width=20
chart.height=20

chart.x_axis.title="日付 2024/12/29-2025/01/03"
chart.y_axis.title="収支"
#---------------------------------------------
# x軸の参照
#---------------------------------------------
x_ax = openpyxl.chart.Reference(ws, min_row=20, max_row=25, min_col=1, max_col=1)


#---------------------------------------------
# 1列目のデータ
#---------------------------------------------
y_ax = openpyxl.chart.Reference(ws, min_row=19, max_row=25, min_col=2, max_col=2) # y軸範囲の参照
data = openpyxl.chart.Series(y_ax, x_ax, title_from_data=True) # x, y軸を対応付けるためまとめる

#data.graphicalProperties.line.noFill = True # 線を消す

#data.marker = openpyxl.chart.marker.Marker('circle') # 点の設定
data.marker.symbol = "circle"
data.marker.size = 10
chart.series.append(data) # chartへの追加

#---------------------------------------------
# 2列目のデータ
#---------------------------------------------
y_ax = openpyxl.chart.Reference(ws, min_row=19, max_row=25, min_col=3, max_col=3) # y軸範囲の参照
data = openpyxl.chart.Series(y_ax, x_ax, title_from_data=True) # x, y軸を対応付けるためまとめる
data.graphicalProperties.line.noFill = True # 線を消す

#{"dot","picture","square","circle","triangle","auto","x","diamond","star","plus","dash"}
#data.marker = openpyxl.chart.marker.Marker('circle') # 点の設定

data.marker.symbol = "diamond"
data.marker.size = 10

chart.series.append(data) # chartへの追加

#---------------------------------------------
# 3列目のデータ
#---------------------------------------------
y_ax = openpyxl.chart.Reference(ws, min_row=19, max_row=25, min_col=4, max_col=4) # y軸範囲の参照
data = openpyxl.chart.Series(y_ax, x_ax, title_from_data=True) # x, y軸を対応付けるためまとめる
data.graphicalProperties.line.noFill = True # 線を消す
#data.marker = openpyxl.chart.marker.Marker('circle') # 点の設定

data.marker.symbol = "square"
data.marker.size = 10

chart.series.append(data) # chartへの追加

#---------------------------------------------
# 4列目のデータ
#---------------------------------------------
y_ax = openpyxl.chart.Reference(ws, min_row=19, max_row=25, min_col=5, max_col=5) # y軸範囲の参照
data = openpyxl.chart.Series(y_ax, x_ax, title_from_data=True) # x, y軸を対応付けるためまとめる
data.graphicalProperties.line.noFill = True # 線を消す
#data.marker = openpyxl.chart.marker.Marker('circle') # 点の設定

data.marker.symbol = "circle"
data.marker.size = 10

chart.series.append(data) # chartへの追加

#---------------------------------------------
# 5列目のデータ
#---------------------------------------------
y_ax = openpyxl.chart.Reference(ws, min_row=19, max_row=25, min_col=6, max_col=6) # y軸範囲の参照
data = openpyxl.chart.Series(y_ax, x_ax, title_from_data=True) # x, y軸を対応付けるためまとめる
data.graphicalProperties.line.noFill = True # 線を消す
#data.marker = openpyxl.chart.marker.Marker('circle') # 点の設定

data.marker.symbol = "triangle"
data.marker.size = 10

chart.series.append(data) # chartへの追加

#---------------------------------------------
# アンカー
#---------------------------------------------
chart.anchor = "I2"
ws.add_chart(chart)


#-----------------------------------------------
# save
#-----------------------------------------------
wb.save(f_name2)



```

解析結果のシート

![analysis.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492810/1544de1f-51ae-dba2-0953-65ef2c150870.png)