LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@fujioka244kogacity(藤岡 毅)

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

Posted at

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

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

次の関数を実行するとEXCELシートにオッズが図のように並びます

make_sheet(urls,s_title,f_name,new)

get_odds(target_url)

odds1229.png

次の関数を実行すると図のように結果がシートに書かれます

make_sheet2(urls,s_title,f_name,new)

get_result(target_url)

result.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

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?