プログラミングのすすめ 第十歩 Windows PowerShell

【paters】

プログラミングのすすめ　第十歩は Windows PowerShell です。

テキスト・データの加工(文字列の操作)には、Linux や Unix の shell コマンドが有利ですが、似たようなものとしてWindows には PowerShell があります。

今回はプログラミングのすすめ　第七歩　プロジェクト FX損益総計グラフ　で扱っていた FX取引の約定履歴データの文字列操作を Windows PowerShell でやって行こうと思います。

加工するテキストは、FXの約定履歴で進めますが、その他一般のテキストデータでも同じように対応出来ることですので、FX約定履歴に拘らずご参照頂けれべばと思います。

やって行きたい要件は　①約定履歴をすべてCSV形式でダウンロードした場合、データには新規約定と決済約定が載っているので決済だけを取り出したい。②ダウンロードの仕方によってはデータに重複した項目が出てくるため、重複項目を省きたい。　

これらの要件をPowerShell でどのように実現できるか見て行きます。最終的には、前回までにVB(Visual Basic)で作ったプログラムもPowerShell だけで代替出来るか試して行きます。　

それでは先ずは PowerShell を詳しくみて行きましょう。

会員数は国内最大級の180万人を突破！【paters】イククルはコチラ!（18禁）

PowerShell の始め方
PowerShell で出来ること、使用するコマンドレットの詳細
Bar Chart（棒グラフ）のデータ
Line Chart（折れ線グラフ）のデータ
グラフのLabelを作成
1. Substring 演算子　
Chart HTML テンプレートにグラフ・データを挿入
まとめ
1. 最後にファイル・クリーンナップ

PowerShell の始め方

それでは、PowerShell の始め方から見て行きましょう。

PowerShell はコマンドプロンプトから　PowerShell とキーインすると始められます。

C:\SumByDay>PowerShell
Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

新しいクロスプラットフォームの PowerShell をお試しください https://aka.ms/pscore6

PS C:\SumByDay>

プロンプト(c:\xxx>)の前に PS と表示されます。

または、スタートメニューから Windows PowerShell をスタートさせることで開始できます。

PowerShell で出来ること、使用するコマンドレットの詳細

まず、やりたいのは新規約定と決済約定の両方が入っている約定履歴から、決済だけを抜き出すことです。

Linux の grep では、　grep “決済” filename で出来てしまう話なのですが、PowerShell ではどうするのでしょうか。

調べてみると、Select-String というコマンドレット(PowerShellでは Cmdlet という）を使うと同じようなことができそうです。PowerShell の help を見てみましょう。PowerShell の helpをみるには、 Get-Help [cmdlet] とキーインします。

ヘルプを表示 Get-Help

簡単なアンケートに答えるだけでポイントが貯まる！貯まったポイントは現金や電子マネーに交換可能♪

運用実績20年以上の信頼！安心のポイントサイト《ワラウ》

Select-String を詳しく調べるため、 Get-Help Select-String とキーインして　Select-String のヘルプを表示してみましょう。

PS C:\SumByDay> get-help select-string

名前                                                                                                                        Select-String                                                                                                                                                                                                                               構文                                                                                                                        Select-String [-Pattern] <string[]> [-Path] <string[]>  [<CommonParameters>]                                        
    Select-String [-Pattern] <string[]>  [<CommonParameters>]

    Select-String [-Pattern] <string[]>  [<CommonParameters>]


エイリアス
    sls


注釈
    Get-Help を実行しましたが、このコンピューターにこのコマンドレットのヘルプ ファイルは見つかりませんでした。ヘルプの
    一部だけが表示されています。
        -- このコマンドレットを含むモジュールのヘルプ ファイルをダウンロードしてインストールするには、Update-Help を使
    用してください。
        -- このコマンドレットのヘルプ トピックをオンラインで確認するには、「Get-Help Select-String -Online」と入力する
    か、
           https://go.microsoft.com/fwlink/?LinkID=113388 を参照してください。

このコマンドのヘルプ　ファイルは見つからなかった　といっています。 Update-Help でヘルプ　ファイルをインストールしてみます。

Update-Help をするとご覧のような画面になり、なにやらインストールされました。

再び、 Get-Help Select-String と入れてみました。

PS C:\SumByDay> get-help select-string

名前
    Select-String

概要
    Finds text in strings and files.


構文
    Select-String [-Pattern] <System.String[]> [-AllMatches] [-CaseSensitive] [-Context <System.Int32[]>] [-Encoding {A
    SCII | BigEndianUnicode | Default | OEM | Unicode | UTF7 | UTF8 | UTF32}] [-Exclude <System.String[]>] [-Include <S
    ystem.String[]>] -InputObject <System.Management.Automation.PSObject> [-List] [-NotMatch] [-Quiet] [-SimpleMatch] [
    <CommonParameters>]

    Select-String [-Pattern] <System.String[]> [-AllMatches] [-CaseSensitive] [-Context <System.Int32[]>] [-Encoding {A
    SCII | BigEndianUnicode | Default | OEM | Unicode | UTF7 | UTF8 | UTF32}] [-Exclude <System.String[]>] [-Include <S
    ystem.String[]>] [-List] -LiteralPath <System.String[]> [-NotMatch] [-Quiet] [-SimpleMatch] [<CommonParameters>]

    Select-String [-Pattern] <System.String[]> [-Path] <System.String[]> [-AllMatches] [-CaseSensitive] [-Context <Syst
    em.Int32[]>] [-Encoding {ASCII | BigEndianUnicode | Default | OEM | Unicode | UTF7 | UTF8 | UTF32}] [-Exclude <Syst
    em.String[]>] [-Include <System.String[]>] [-List] [-NotMatch] [-Quiet] [-SimpleMatch] [<CommonParameters>]


説明
    The `Select-String` cmdlet uses regular expression matching to search for text patterns in input strings and files.
     You can use `Select-String` similar to `grep` in UNIX or `findstr.exe` in Windows.

    `Select-String` is based on lines of text. By default, `Select-String` finds the first match in each line and, for
    each match, it displays the file name, line number, and all text in the line containing the match. You can direct `
    Select-String` to find multiple matches per line, display text before and after the match, or display a Boolean val
    ue (True or False) that indicates whether a match is found.

    `Select-String` can display all the text matches or stop after the first match in each input file. `Select-String`
    can be used to display all text that doesn't match the specified pattern.

    You can also specify that `Select-String` should expect a particular character encoding, such as when you're search
    ing files of Unicode text. `Select-String` uses the byte-order-mark (BOM) to detect the encoding format of the file
    . If the file has no BOM, it assumes the encoding is UTF8.


関連するリンク
    Online Version: https://learn.microsoft.com/powershell/module/microsoft.powershell.utility/select-string?view=power
    shell-5.1&WT.mc_id=ps-gethelp
    about_Automatic_Variables

今度は、かなり長いヘルプが表示されました。ここには書ききれていません。

概要
Finds text in strings and files.

ですから、Linux の grep と同じような働きです。

例を参照するには、次のように入力してください: “get-help Select-String -examples”.　と書かれています。

PS C:\SumByDay> get-help Select-String -examples

名前
    Select-String

概要
    Finds text in strings and files.


    ------------ Example 1: Find a case-sensitive match ------------

    'Hello', 'HELLO' | Select-String -Pattern 'HELLO' -CaseSensitive -SimpleMatch

    The text strings Hello and HELLO are sent down the pipeline to the `Select-String` cmdlet. `Select-String` uses the
     Pattern parameter to specify HELLO . The CaseSensitive parameter specifies that the case must match only the upper
    -case pattern. SimpleMatch is an optional parameter and specifies that the string in the pattern isn't interpreted
    as a regular expression. `Select-String` displays HELLO in the PowerShell console.
    ------------ Example 2: Find matches in text files ------------

    Get-Alias | Out-File -FilePath .\Alias.txt
    Get-Command | Out-File -FilePath .\Command.txt
    Select-String -Path .\*.txt -Pattern 'Get-'

    Alias.txt:8:Alias            cat -> Get-Content
    Alias.txt:28:Alias           dir -> Get-ChildItem
    Alias.txt:43:Alias           gal -> Get-Alias
    Command.txt:966:Cmdlet       Get-Acl
    Command.txt:967:Cmdlet       Get-Alias

    In this example, `Get-Alias` and `Get-Command` are used with the `Out-File` cmdlet to create two text files in the
    current directory, Alias.txt and Command.txt .

またまた、かなり長い説明が出てきました。

———— Example 2: Find matches in text files ————

Get-Alias | Out-File -FilePath .\Alias.txt
Get-Command | Out-File -FilePath .\Command.txt
Select-String -Path .\*.txt -Pattern 'Get-'

この Example 2:Find matches in text files が求めている形態のようです。

この Example の Get-Alias | … と Get-Command | … は、それぞれ Get-.. のアウトプットを Alias.txt と Command.txt に書き出すようです。Get-Alias は、コマンドの省略名の定義を表示するコマンドです。

この二つの .txt ファイルの中を ‘Get-‘ で探してマッチする行を表示させるのが Select-String -Path .\*.txt -Pattern ‘Get-‘ ですね。

ついでにオンラインのヘルプを表示するには、 Get-Help Select-String -Online とキーインします。オンラインのヘルプも見てみましょう。

ここで Get-Alias やパイプライン “|” が出てきましたので、こちらも見て行きましょう。

エイリアスを知る(コマンドレットの省略表記) Get-Alias

概要
Gets the aliases for the current session.

現在のセッションのエイリアスを取得します。

Get-Alias とだけ入れると、現セッションで定義されているエイリアスのリストが表示されます。

PS C:\sumbyday> Get-Alias

CommandType     Name                                               Version    Source
-----------     ----                                               -------    ------
Alias           % -> ForEach-Object
Alias           ? -> Where-Object
Alias           ac -> Add-Content
Alias           asnp -> Add-PSSnapin
Alias           cat -> Get-Content
Alias           cd -> Set-Location
Alias           CFS -> ConvertFrom-String                          3.1.0.0    Microsoft.PowerShell.Utility
Alias           chdir -> Set-Location
Alias           clc -> Clear-Content
Alias           clear -> Clear-Host

Get-Alias ls のようにエイリアスを渡すとそのエイリアスに対するコマンドレットを表示します。

PS C:\sumbyday> Get-Alias ls

CommandType     Name                                               Version    Source
-----------     ----                                               -------    ------
Alias           ls -> Get-ChildItem

Get-Alias -Definition Get-ChildItem のように -Definition でコマンドレットを指定すると、そのコマンドレットに定義されているエイリアスが表示されます。

PS C:\sumbyday> Get-Alias -Definition Get-ChildItem

CommandType     Name                                               Version    Source
-----------     ----                                               -------    ------
Alias           dir -> Get-ChildItem
Alias           gci -> Get-ChildItem
Alias           ls -> Get-ChildItem

ご覧のように Get-ChildItem には、ls の他に dir , gci がエイリアスとして定義されていることが分かります。

Select-String には、エイリアスが定義されているのかどうか見てみましょう。

PS C:\sumbyday> Get-Alias -Definition Select-String

CommandType     Name                                               Version    Source
-----------     ----                                               -------    ------
Alias           sls -> Select-String

Select-String には、 sls というエイリアスが定義されています。

パイプライン　” | “　、オブジェクトとストリングの違い

マッチングなしでメッセージ可能ハッピーメール(18禁)

Linux 等で、 cat test.txt | grep “abc” とやると test.txt の内容を grep に渡して “abc”　にマッチする行だけを表示する　という動きをするのですが、PowerShell のパイプラインは、ストリングを渡すのではなく　Object を渡すので Linux 等と動きが違います。

試しに、 ls | sls -Pattern “.txt” とやってみましょう。その前に、カレントディレクトリを確認してみます。

PS C:\sumbyday> ls


    ディレクトリ: C:\sumbyday


Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
d-----        2022/10/27     14:16                All_Data
d-----        2022/10/27     15:03                aug
d-----        2022/10/16     17:17                sep
d-----        2022/10/16     20:55                testJson
-a----        2022/10/14      1:50             87 Chart.css
-a----        2022/10/28      0:21         370758 Command.txt
-a----        2022/10/17     16:56           1467 MixedChartTemplate.html
-a----        2022/10/27     15:46           1353 MixedChartWdata.html
-a----        2022/10/24     11:07            110 SumByDay.bat
-a----        2022/10/16     17:20       58504498 SumByDay.exe
-a----        2022/10/27     17:50           3518 test.csv
-a----        2022/10/09     21:42          19314 TradeRecordList.csv
-a----        2022/10/27     15:46            147 TradeSumByDay.txt
-a----        2022/10/27     15:46            147 TradeSumByDayS.txt
-a----        2022/10/24     11:03       58504516 UpdateGdata.exe

カレント・ディレクトリには、Command.txt , TradeSumByDay.txt , TradeSumByDayS.txt の三つの “.txt” ファイルがありますので、 ls | sls -Pattern ”.txt” の結果は、この三つの “.txt” ファイルが表示されることが期待値です。

PS C:\sumbyday> ls | sls -Pattern ".txt"

SumByDay.bat:3:sort <TradeSumByDay.txt >TradeSumByDayS.txt
SumByDay.exe:88557:�d��,TXT)u- $(,048<@DHLPTX\`dhlptx|��������������������������������    
!)19AIQYaiqy����������������    !)19AIQYaiq���������
��� \(#0\,#`\4#�\8#�\8#�\<#�\@# ]X#`]d#�]t#0^�#�^�#_�#
�_�#`�#�`�#a�#@a$Pa$`a$pa$�a$�a$�a$�a$�a$�a
$�a$ b(#0b$`b$�b $�b($�b4$ c@$@c(#PcH$`cL$�cP$�cT

何やら化け化けの文字が出てきてしまいました。これは、前述したようにパイプラインがストリングではなく Object を渡すからです。

オブジェクトをストリングに変換 Out-String

老舗サイトのラブサーチ[18歳以上]

入力オブジェクトを文字列として出力します。オンライン・ヘルプ

Object を String に変換するために PowerShell には、Out-String というコマンドレットが用意されています。Out-String -Stream は短縮形 OSS と記述できます。

ls のアウトプットをストリングに変換するため、次のように変更してみます。　ls | OSS | sls -Pattern “.txt”

PS C:\sumbyday> ls | OSS | sls -Pattern ".txt"

-a----        2022/10/28      0:21         370758 Command.txt
-a----        2022/10/27     15:46            147 TradeSumByDay.txt
-a----        2022/10/27     15:46            147 TradeSumByDayS.txt

期待通りに３個のファイルがリストされました。out-string -stream で受け取った object をストリングにして stream に出力しているからです。

文字列の検索(Grep like)　Select-String

これまでで、Select_String が Linux の Grep like であると分かりました。Select_String のエイリアスは sls です。

それでは、実際にやってみましょう。

まず、テストする CSV ファイルです。　test.csv には、 “決済” と “新規” の両方の約定履歴が入っています。

PS C:\sumbyday> cat test.csv
"通貨ペア","売買","区分","数量（Lot）","約定レート","建玉損益（円）","スワップ","決済損益（円）","注文日時","約定日時","注文番号","円転レート","取引手数料","建玉損益"
"AUD/JPY","売","決済","1","92.733","-1,430","0","-1,430","2022/10/17 16:22:01","2022/10/17 16:22:01","126000006438824","-","0","\-1,430"
"AUD/JPY","買","新規","1","92.876","","","","2022/10/17 16:06:57","2022/10/17 16:06:57","126000006438380","","0",""
"AUD/JPY","買","決済","1","92.878","-280","0","-280","2022/10/17 16:06:39","2022/10/17 16:06:39","126000006438367","-","0","\-280"
"AUD/JPY","売","新規","1","92.85","","","","2022/10/17 16:06:00","2022/10/17 16:06:00","126000006438336","","0",""
"AUD/JPY","買","決済","1","92.882","-20","0","-20","2022/10/17 16:05:22","2022/10/17 16:05:22","126000006438307","-","0","\-20"
"AUD/JPY","売","新規","1","92.88","","","","2022/10/17 16:05:10","2022/10/17 16:05:10","126000006438294","","0",""
"AUD/JPY","売","新規","1","92.88","","","","2022/10/17 16:05:10","2022/10/17 16:05:10","126000006438294","","0",""
"AUD/JPY","売","決済","1","92.874","20","0","20","2022/10/17 16:04:09","2022/10/17 16:04:09","126000006438259","-","0","\20"
"AUD/JPY","買","新規","1","92.872","","","","2022/10/17 16:03:29","2022/10/17 16:03:29","126000006438223","","0",""
"AUD/JPY","売","決済","1","92.822","150","0","150","2022/10/17 16:02:27","2022/10/17 16:02:27","126000006438163","-","0","\150"
"AUD/JPY","買","新規","1","92.807","","","","2022/10/17 16:02:00","2022/10/17 16:02:00","126000006438134","","0",""
"AUD/JPY","買","決済","1","92.687","110","0","110","2022/10/17 15:54:43","2022/10/17 15:54:43","126000006437950","-","0","\110"
"AUD/JPY","売","新規","1","92.698","","","","2022/10/17 15:47:38","2022/10/17 15:47:38","126000006437804","","0",""
"AUD/JPY","売","決済","1","92.707","930","0","930","2022/10/17 15:46:17","2022/10/17 15:46:17","126000006437788","-","0","\930"
"AUD/JPY","買","新規","1","92.614","","","","2022/10/17 11:01:43","2022/10/17 11:02:02","126000006433419","","0",""
"AUD/JPY","買","決済","1","92.62","-870","0","-870","2022/10/17 10:56:44","2022/10/17 10:56:44","126000006433377","-","0","\-870"
"AUD/JPY","買","新規","1","92.807","","","","2022/10/17 16:02:00","2022/10/17 16:02:00","126000006438134","","0",""
"AUD/JPY","売","新規","1","92.533","","","","2022/10/17 10:33:51","2022/10/17 10:33:51","126000006433142","","0",""

sls -Pattern “決済” test.csv　で “決済” の約定だけを抜き出します。

PS C:\sumbyday> sls -Pattern "決済" test.csv

test.csv:1:"通貨ペア","売買","区分","数量（Lot）","約定レート","建玉損益（円）","スワップ","決済損益（円）","注文日時","約定日時","注文番号","円転レート","取引手数料","建玉損益"
test.csv:2:"AUD/JPY","売","決済","1","92.733","-1,430","0","-1,430","2022/10/17 16:22:01","2022/10/17 16:22:01","126000006438824","-","0","\-1,430"
test.csv:4:"AUD/JPY","買","決済","1","92.878","-280","0","-280","2022/10/17 16:06:39","2022/10/17 16:06:39","126000006438367","-","0","\-280"
test.csv:6:"AUD/JPY","買","決済","1","92.882","-20","0","-20","2022/10/17 16:05:22","2022/10/17 16:05:22","126000006438307","-","0","\-20"
test.csv:9:"AUD/JPY","売","決済","1","92.874","20","0","20","2022/10/17 16:04:09","2022/10/17 16:04:09","126000006438259","-","0","\20"
test.csv:11:"AUD/JPY","売","決済","1","92.822","150","0","150","2022/10/17 16:02:27","2022/10/17 16:02:27","126000006438163","-","0","\150"
test.csv:13:"AUD/JPY","買","決済","1","92.687","110","0","110","2022/10/17 15:54:43","2022/10/17 15:54:43","126000006437950","-","0","\110"
test.csv:15:"AUD/JPY","売","決済","1","92.707","930","0","930","2022/10/17 15:46:17","2022/10/17 15:46:17","126000006437788","-","0","\930"
test.csv:17:"AUD/JPY","買","決済","1","92.62","-870","0","-870","2022/10/17 10:56:44","2022/10/17 10:56:44","126000006433377","-","0","\-870"
test.csv:20:"AUD/JPY","買","決済","1","92.57","-2,210","0","-2,210","2022/10/17 10:32:06","2022/10/17 10:32:06","126000006433108","-","0","\-2,210"
test.csv:22:"AUD/JPY","買","決済","1","92.453","110","0","110","2022/10/17 09:41:51","2022/10/17 09:41:51","126000006432146","-","0","\110"
test.csv:24:"AUD/JPY","売","決済","1","92.457","-300","0","-300","2022/10/17 09:11:45","2022/10/17 09:11:45","126000006431663","-","0","\-300"
test.csv:26:"AUD/JPY","買","決済","1","92.538","-500","0","-500","2022/10/17 09:07:30","2022/10/17 09:07:30","126000006431541","-","0","\-500"

“決済”だけを抜き出していますが、先頭にファイル名と行番号が入っています。これは必要ないので省きたいです。方法を調べましたが、オプションやパラメータを変えて省くことは出来ません。

オブジェクトをリスト・アップ　Format-List

前述したようにPowerShell の出力は　Object形式です。Select-String の出力のObject形式がどのようになっているのかを調べてみましょう。

sls -Pattern “決済” test.csv | Format-List として、 Select-String の出力を Format-List コマンドレットに渡すことで Object形式を調べることが出来ます。Format-List のエイリアスは fl です。

PS C:\sumbyday> get-Alias -definition Format-List

CommandType     Name                                               Version    Source
-----------     ----                                               -------    ------
Alias           fl -> Format-List


PS C:\sumbyday> sls -Pattern "決済" test.csv | fl

IgnoreCase : True
LineNumber : 1
Line       : "通貨ペア","売買","区分","数量（Lot）","約定レート","建玉損益（円）","スワップ","決済損益（円）","注文日時","約定日時","注文番号","円転レート","取引手数料","建玉損益"
Filename   : test.csv
Path       : C:\sumbyday\test.csv
Pattern    : 決済
Context    :
Matches    : {0}

IgnoreCase : True
LineNumber : 2
Line       : "AUD/JPY","売","決済","1","92.733","-1,430","0","-1,430","2022/10/17 16:22:01","2022/10/17 16:22:01","126000006438824","-","0","\-1,430"
Filename   : test.csv
Path       : C:\sumbyday\test.csv
Pattern    : 決済
Context    :

ご覧のように行のデータは、Line というプロパティ名です。

オブジェクトを選択　Select-Object

マッチングなしでメッセージ可能ハッピーメール(18禁)

PowerShell には、Select-Object というコマンドレットがあります。Select-Objectでオブジェクトやオブジェクトの中のプロパティを選択することが出来ます。

Select-String のアウトプットは、前項の表示のように IgnoreCase,LineNumber,Line,Filename,Path,Pattern,Context,Matches の８個のプロパティです。この内の Line が必要とする行データです。Line だけを　Select-Object で選択して出すようにします。Select-Object のエイリアスは、 select です。

sls -Pattern “決済” test.csv | select Line これで行データだけが出る筈です。

PS C:\sumbyday> get-Alias -Definition Select-Object

CommandType     Name                                               Version    Source
-----------     ----                                               -------    ------
Alias           select -> Select-Object

PS C:\sumbyday> sls -Pattern "決済" test.csv | select Line

Line
----
"通貨ペア","売買","区分","数量（Lot）","約定レート","建玉損益（円）","スワップ","決済損益（円）","注文日時","約定日時","注文番号","円転レート","取引手数料","建玉損益"
"AUD/JPY","売","決済","1","92.733","-1,430","0","-1,430","2022/10/17 16:22:01","2022/10/17 16:22:01","126000006438824","-","0","\-1,430"
"AUD/JPY","買","決済","1","92.878","-280","0","-280","2022/10/17 16:06:39","2022/10/17 16:06:39","126000006438367","-","0","\-280"
"AUD/JPY","買","決済","1","92.882","-20","0","-20","2022/10/17 16:05:22","2022/10/17 16:05:22","126000006438307","-","0","\-20"
"AUD/JPY","売","決済","1","92.874","20","0","20","2022/10/17 16:04:09","2022/10/17 16:04:09","126000006438259","-","0","\20"
"AUD/JPY","売","決済","1","92.822","150","0","150","2022/10/17 16:02:27","2022/10/17 16:02:27","126000006438163","-","0","\150"
"AUD/JPY","買","決済","1","92.687","110","0","110","2022/10/17 15:54:43","2022/10/17 15:54:43","126000006437950","-","0","\110"
"AUD/JPY","売","決済","1","92.707","930","0","930","2022/10/17 15:46:17","2022/10/17 15:46:17","126000006437788","-","0","\930"
"AUD/JPY","買","決済","1","92.62","-870","0","-870","2022/10/17 10:56:44","2022/10/17 10:56:44","126000006433377","-","0","\-870"
"AUD/JPY","買","決済","1","92.57","-2,210","0","-2,210","2022/10/17 10:32:06","2022/10/17 10:32:06","126000006433108","-","0","\-2,210"
"AUD/JPY","買","決済","1","92.453","110","0","110","2022/10/17 09:41:51","2022/10/17 09:41:51","126000006432146","-","0","\110"
"AUD/JPY","売","決済","1","92.457","-300","0","-300","2022/10/17 09:11:45","2022/10/17 09:11:45","126000006431663","-","0","\-300"
"AUD/JPY","買","決済","1","92.538","-500","0","-500","2022/10/17 09:07:30","2022/10/17 09:07:30","126000006431541","-","0","\-500"

これで”決済”だけを抜き出すことが出来るようになりました。

別の方法で、ファイルの内容を cat (Get-Content) して、パイプラインで Out-String -Stream に渡して、更に Select-String で ”決済”　でマッチした行だけを抜き出せば、出力にファイル名、行番号は入りません。

PS C:\sumbyday> cat test.csv | oss | sls "決済"

"通貨ペア","売買","区分","数量（Lot）","約定レート","建玉損益（円）","スワップ","決済損益（円）","注文日時","約定日時","注文番号","円転レート","取引手数料","建玉
損益"
"AUD/JPY","売","決済","1","92.733","-1,430","0","-1,430","2022/10/17 16:22:01","2022/10/17 16:22:01","126000006438824","-","0","\-1,430"
"AUD/JPY","買","決済","1","92.878","-280","0","-280","2022/10/17 16:06:39","2022/10/17 16:06:39","126000006438367","-","0","\-280"
"AUD/JPY","買","決済","1","92.882","-20","0","-20","2022/10/17 16:05:22","2022/10/17 16:05:22","126000006438307","-","0","\-20"
"AUD/JPY","売","決済","1","92.874","20","0","20","2022/10/17 16:04:09","2022/10/17 16:04:09","126000006438259","-","0","\20"
"AUD/JPY","売","決済","1","92.822","150","0","150","2022/10/17 16:02:27","2022/10/17 16:02:27","126000006438163","-","0","\150"
"AUD/JPY","買","決済","1","92.687","110","0","110","2022/10/17 15:54:43","2022/10/17 15:54:43","126000006437950","-","0","\110"
"AUD/JPY","売","決済","1","92.707","930","0","930","2022/10/17 15:46:17","2022/10/17 15:46:17","126000006437788","-","0","\930"
"AUD/JPY","買","決済","1","92.62","-870","0","-870","2022/10/17 10:56:44","2022/10/17 10:56:44","126000006433377","-","0","\-870"
"AUD/JPY","買","決済","1","92.57","-2,210","0","-2,210","2022/10/17 10:32:06","2022/10/17 10:32:06","126000006433108","-","0","\-2,210"
"AUD/JPY","買","決済","1","92.453","110","0","110","2022/10/17 09:41:51","2022/10/17 09:41:51","126000006432146","-","0","\110"
"AUD/JPY","売","決済","1","92.457","-300","0","-300","2022/10/17 09:11:45","2022/10/17 09:11:45","126000006431663","-","0","\-300"
"AUD/JPY","買","決済","1","92.538","-500","0","-500","2022/10/17 09:07:30","2022/10/17 09:07:30","126000006431541","-","0","\-500"
"AUD/JPY","買","決済","1","92.882","-20","0","-20","2022/10/17 16:05:22","2022/10/17 16:05:22","126000006438307","-","0","\-20"

ファイルに書き出す　Out-File

期待通りのアウトプットを得ることが出来ましたので、ファイルに書き出してみます。リダイレクト “>” してもいいのですが、PowerShell では、 Out-File コマンドレットが用意されているので、こちらを使ってみます。

sls -Pattern “決済” test.csv | select Line | Out-File OnlyKessai.csv 　OnlyKessai.csv という名前のファイルで書き出します。

PS C:\sumbyday> sls -Pattern "決済" test.csv | select Line | Out-File OnlyKessai.csv
PS C:\sumbyday> ls
    ディレクトリ: C:\sumbyday
Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
d-----        2022/10/27     14:16                All_Data
d-----        2022/10/27     15:03                aug
d-----        2022/10/16     17:17                sep
d-----        2022/10/16     20:55                testJson
-a----        2022/10/14      1:50             87 Chart.css
-a----        2022/10/28      0:21         370758 Command.txt
-a----        2022/10/17     16:56           1467 MixedChartTemplate.html
-a----        2022/10/27     15:46           1353 MixedChartWdata.html
-a----        2022/10/28     22:30           4064 OnlyKessai.csv
-a----        2022/10/24     11:07            110 SumByDay.bat
-a----        2022/10/16     17:20       58504498 SumByDay.exe
-a----        2022/10/27     17:50           3518 test.csv
-a----        2022/10/09     21:42          19314 TradeRecordList.csv
-a----        2022/10/27     15:46            147 TradeSumByDay.txt
-a----        2022/10/27     15:46            147 TradeSumByDayS.txt
-a----        2022/10/24     11:03       58504516 UpdateGdata.exe

ファイルは出来ています。

中身を　cat(Get-Content) で見てみましょう。

PS C:\sumbyday> cat OnlyKessai.csv

Line
----
"通貨ペア","売買","区分","数量（Lot）","約定レート","建玉損益（円）","スワップ","決済損益（円）","注文日時","約定日時","注文番号","円転レート","取引手数料","建玉損益"                      
"AUD/JPY","売","決済","1","92.733","-1,430","0","-1,430","2022/10/17 16:22:01","2022/10/17 16:22:01","126000006438824","-","0","\-1,430"
"AUD/JPY","買","決済","1","92.878","-280","0","-280","2022/10/17 16:06:39","2022/10/17 16:06:39","126000006438367","-","0","\-280"
"AUD/JPY","買","決済","1","92.882","-20","0","-20","2022/10/17 16:05:22","2022/10/17 16:05:22","126000006438307","-","0","\-20"
"AUD/JPY","売","決済","1","92.874","20","0","20","2022/10/17 16:04:09","2022/10/17 16:04:09","126000006438259","-","0","\20"
"AUD/JPY","売","決済","1","92.822","150","0","150","2022/10/17 16:02:27","2022/10/17 16:02:27","126000006438163","-","0","\150"
"AUD/JPY","買","決済","1","92.687","110","0","110","2022/10/17 15:54:43","2022/10/17 15:54:43","126000006437950","-","0","\110"
"AUD/JPY","売","決済","1","92.707","930","0","930","2022/10/17 15:46:17","2022/10/17 15:46:17","126000006437788","-","0","\930"
"AUD/JPY","買","決済","1","92.62","-870","0","-870","2022/10/17 10:56:44","2022/10/17 10:56:44","126000006433377","-","0","\-870"
"AUD/JPY","買","決済","1","92.57","-2,210","0","-2,210","2022/10/17 10:32:06","2022/10/17 10:32:06","126000006433108","-","0","\-2,210"
"AUD/JPY","買","決済","1","92.453","110","0","110","2022/10/17 09:41:51","2022/10/17 09:41:51","126000006432146","-","0","\110"
"AUD/JPY","売","決済","1","92.457","-300","0","-300","2022/10/17 09:11:45","2022/10/17 09:11:45","126000006431663","-","0","\-300"
"AUD/JPY","買","決済","1","92.538","-500","0","-500","2022/10/17 09:07:30","2022/10/17 09:07:30","126000006431541","-","0","\-500"

内容も期待通りですが、1～3行目は、不要ですので通貨ペアの　JPY で grep して、1~3行を省きます。(日本円以外では取引しない事が条件です）

sls -Pattern “決済” test.csv | select Line | oss | sls JPY | Out-File OnlyKessai.csv

PS C:\sumbyday> sls -Pattern "決済" test.csv | select Line | oss | sls JPY | Out-File OnlyKessai.csv
PS C:\sumbyday> cat OnlyKessai.csv

"AUD/JPY","売","決済","1","92.733","-1,430","0","-1,430","2022/10/17 16:22:01","2022/10/17 16:22:01","126000006438824","-","0","\-1,430"
"AUD/JPY","買","決済","1","92.878","-280","0","-280","2022/10/17 16:06:39","2022/10/17 16:06:39","126000006438367","-","0","\-280"
"AUD/JPY","買","決済","1","92.882","-20","0","-20","2022/10/17 16:05:22","2022/10/17 16:05:22","126000006438307","-","0","\-20"
"AUD/JPY","売","決済","1","92.874","20","0","20","2022/10/17 16:04:09","2022/10/17 16:04:09","126000006438259","-","0","\20"
"AUD/JPY","売","決済","1","92.822","150","0","150","2022/10/17 16:02:27","2022/10/17 16:02:27","126000006438163","-","0","\150"
"AUD/JPY","買","決済","1","92.687","110","0","110","2022/10/17 15:54:43","2022/10/17 15:54:43","126000006437950","-","0","\110"
"AUD/JPY","売","決済","1","92.707","930","0","930","2022/10/17 15:46:17","2022/10/17 15:46:17","126000006437788","-","0","\930"
"AUD/JPY","買","決済","1","92.62","-870","0","-870","2022/10/17 10:56:44","2022/10/17 10:56:44","126000006433377","-","0","\-870"
"AUD/JPY","買","決済","1","92.57","-2,210","0","-2,210","2022/10/17 10:32:06","2022/10/17 10:32:06","126000006433108","-","0","\-2,210"
"AUD/JPY","買","決済","1","92.453","110","0","110","2022/10/17 09:41:51","2022/10/17 09:41:51","126000006432146","-","0","\110"
"AUD/JPY","売","決済","1","92.457","-300","0","-300","2022/10/17 09:11:45","2022/10/17 09:11:45","126000006431663","-","0","\-300"
"AUD/JPY","買","決済","1","92.538","-500","0","-500","2022/10/17 09:07:30","2022/10/17 09:07:30","126000006431541","-","0","\-500"
"AUD/JPY","買","決済","1","92.882","-20","0","-20","2022/10/17 16:05:22","2022/10/17 16:05:22","126000006438307","-","0","\-20"
"AUD/JPY","売","決済","1","92.874","20","0","20","2022/10/17 16:04:09","2022/10/17 16:04:09","126000006438259","-","0","\20"
"AUD/JPY","売","決済","1","92.822","150","0","150","2022/10/17 16:02:27","2022/10/17 16:02:27","126000006438163","-","0","\150"
"AUD/JPY","買","決済","1","92.687","110","0","110","2022/10/17 15:54:43","2022/10/17 15:54:43","126000006437950","-","0","\110"

CSV形式を読み込む Import-Csv , オブジェクトでソート　Sort-Object

約定履歴のダウンロードの仕方によっては、次のようにデータが重複することがあります。

"AUD/JPY","売","決済","1","92.733","-1,430","0","-1,430","2022/10/17 16:22:01","2022/10/17 16:22:01","126000006438824","-","0","\-1,430"
"AUD/JPY","買","決済","1","92.878","-280","0","-280","2022/10/17 16:06:39","2022/10/17 16:06:39","126000006438367","-","0","\-280"
"AUD/JPY","買","決済","1","92.882","-20","0","-20","2022/10/17 16:05:22","2022/10/17 16:05:22","126000006438307","-","0","\-20"
"AUD/JPY","売","決済","1","92.874","20","0","20","2022/10/17 16:04:09","2022/10/17 16:04:09","126000006438259","-","0","\20"
"AUD/JPY","売","決済","1","92.822","150","0","150","2022/10/17 16:02:27","2022/10/17 16:02:27","126000006438163","-","0","\150"
"AUD/JPY","買","決済","1","92.687","110","0","110","2022/10/17 15:54:43","2022/10/17 15:54:43","126000006437950","-","0","\110"
"AUD/JPY","売","決済","1","92.707","930","0","930","2022/10/17 15:46:17","2022/10/17 15:46:17","126000006437788","-","0","\930"
"AUD/JPY","買","決済","1","92.62","-870","0","-870","2022/10/17 10:56:44","2022/10/17 10:56:44","126000006433377","-","0","\-870"
"AUD/JPY","買","決済","1","92.57","-2,210","0","-2,210","2022/10/17 10:32:06","2022/10/17 10:32:06","126000006433108","-","0","\-2,210"
"AUD/JPY","買","決済","1","92.453","110","0","110","2022/10/17 09:41:51","2022/10/17 09:41:51","126000006432146","-","0","\110"
"AUD/JPY","売","決済","1","92.457","-300","0","-300","2022/10/17 09:11:45","2022/10/17 09:11:45","126000006431663","-","0","\-300"
"AUD/JPY","買","決済","1","92.538","-500","0","-500","2022/10/17 09:07:30","2022/10/17 09:07:30","126000006431541","-","0","\-500"
"AUD/JPY","買","決済","1","92.882","-20","0","-20","2022/10/17 16:05:22","2022/10/17 16:05:22","126000006438307","-","0","\-20"
"AUD/JPY","売","決済","1","92.874","20","0","20","2022/10/17 16:04:09","2022/10/17 16:04:09","126000006438259","-","0","\20"
"AUD/JPY","売","決済","1","92.822","150","0","150","2022/10/17 16:02:27","2022/10/17 16:02:27","126000006438163","-","0","\150"
"AUD/JPY","買","決済","1","92.687","110","0","110","2022/10/17 15:54:43","2022/10/17 15:54:43","126000006437950","-","0","\110"

3~6行目　と　13~16行目　のデータは重複しています。

重複を省くためには、まず約定日時でsortして重複項目をまとめる必要があります。

sort するために、次のコマンドでCSVファイルをインポートします。

Import-Csv OnlyKessai.csv -Header H1,H2,H3,H4,H5,H6,H7,H8,H9,H10

項目が14個あるのですが必要なデータは10項目目の約定日時までなので Header は 10個まで指定します。残りの項目はインポートされずに破棄されます。

PS C:\sumbyday> Import-Csv OnlyKessai.csv -Header H1,H2,H3,H4,H5,H6,H7,H8,H9,H10
H1  : AUD/JPY
H2  : 売
H3  : 決済
H4  : 1
H5  : 92.733
H6  : -1,430
H7  : 0
H8  : -1,430
H9  : 2022/10/17 16:22:01
H10 : 2022/10/17 16:22:01

H1  : AUD/JPY
H2  : 買
H3  : 決済
H4  : 1
H5  : 92.878
H6  : -280
H7  : 0
H8  : -280
H9  : 2022/10/17 16:06:39
H10 : 2022/10/17 16:06:39

各項目は、Header名(H1,H2,::,Hn)の Object として読み込まれています。

H10 が約定日時ですので、パイプラインで Sort-Object に渡して Object H10 で sort してみます。

Import-Csv OnlyKessai.csv -Header H1,H2,H3,H4,H5,H6,H7,H8,H9,H10 | Sort-Object H10

PS C:\sumbyday> Import-Csv OnlyKessai.csv -Header H1,H2,H3,H4,H5,H6,H7,H8,H9,H10 | Sort-Object H10
H1  : AUD/JPY
H2  : 買
H3  : 決済
H4  : 1
H5  : 92.538
H6  : -500
H7  : 0
H8  : -500
H9  : 2022/10/17 09:07:30
H10 : 2022/10/17 09:07:30

H1  : AUD/JPY
H2  : 売
H3  : 決済
H4  : 1
H5  : 92.457
H6  : -300
H7  : 0
H8  : -300
H9  : 2022/10/17 09:11:45
H10 : 2022/10/17 09:11:45

H1  : AUD/JPY
H2  : 買
H3  : 決済
H4  : 1
H5  : 92.453
H6  : 110
H7  : 0
H8  : 110
H9  : 2022/10/17 09:41:51
H10 : 2022/10/17 09:41:51

H10 の内容で正順に並んでいるようです。

CSV形式で書き出し　Export-Csv

イククルはコチラ!（18禁）

ソートが終わったデータを CSV形式で出力してみます。

次のように結果をパイプラインで Export-Csv に渡して OnlyKessaiS.csv というファイルに書き出します。漢字が文字化けしないように　-Encoding UTF8 で書き出します。

Import-Csv OnlyKessai.csv -Header H1,H2,H3,H4,H5,H6,H7,H8,H9,H10 | Sort-Object H10 | Export-Csv OnlyKessaiS.csv -Encoding UTF8


PS C:\sumbyday> Import-Csv OnlyKessai.csv -Header H1,H2,H3,H4,H5,H6,H7,H8,H9,H10 | Sort-Object H10 | Export-Csv OnlyKessaiS.csv -Encoding UTF8
PS C:\sumbyday> cat OnlyKessaiS.csv
#TYPE System.Management.Automation.PSCustomObject
"H1","H2","H3","H4","H5","H6","H7","H8","H9","H10"
"AUD/JPY","買","決済","1","92.538","-500","0","-500","2022/10/17 09:07:30","2022/10/17 09:07:30"
"AUD/JPY","売","決済","1","92.457","-300","0","-300","2022/10/17 09:11:45","2022/10/17 09:11:45"
"AUD/JPY","買","決済","1","92.453","110","0","110","2022/10/17 09:41:51","2022/10/17 09:41:51"
"AUD/JPY","買","決済","1","92.57","-2,210","0","-2,210","2022/10/17 10:32:06","2022/10/17 10:32:06"
"AUD/JPY","買","決済","1","92.62","-870","0","-870","2022/10/17 10:56:44","2022/10/17 10:56:44"
"AUD/JPY","売","決済","1","92.707","930","0","930","2022/10/17 15:46:17","2022/10/17 15:46:17"
"AUD/JPY","買","決済","1","92.687","110","0","110","2022/10/17 15:54:43","2022/10/17 15:54:43"
"AUD/JPY","買","決済","1","92.687","110","0","110","2022/10/17 15:54:43","2022/10/17 15:54:43"
"AUD/JPY","売","決済","1","92.822","150","0","150","2022/10/17 16:02:27","2022/10/17 16:02:27"
"AUD/JPY","売","決済","1","92.822","150","0","150","2022/10/17 16:02:27","2022/10/17 16:02:27"
"AUD/JPY","売","決済","1","92.874","20","0","20","2022/10/17 16:04:09","2022/10/17 16:04:09"
"AUD/JPY","売","決済","1","92.874","20","0","20","2022/10/17 16:04:09","2022/10/17 16:04:09"
"AUD/JPY","買","決済","1","92.882","-20","0","-20","2022/10/17 16:05:22","2022/10/17 16:05:22"
"AUD/JPY","買","決済","1","92.882","-20","0","-20","2022/10/17 16:05:22","2022/10/17 16:05:22"
"AUD/JPY","買","決済","1","92.878","-280","0","-280","2022/10/17 16:06:39","2022/10/17 16:06:39"
"AUD/JPY","売","決済","1","92.733","-1,430","0","-1,430","2022/10/17 16:22:01","2022/10/17 16:22:01"

12~19行目の間のデータは、同じものが２行づつあります。この状態に出来れば重複した行をUnique コマンドで一つにできます。

重複行を排除　Get-Unique

PowerShell でも Unique コマンドが多分あるだろうと見当をつけて Get-Alias を見てみました。

PS C:\sumbyday> get-alias | oss | sls unique

Alias           gu -> Get-Unique

Get-Unique というコマンドレットがあります。エイリアスは gu です。

PS C:\sumbyday> cat OnlyKessaiS.csv | gu | oss | sls JPY

"AUD/JPY","買","決済","1","92.538","-500","0","-500","2022/10/17 09:07:30","2022/10/17 09:07:30","126000006431541","-","0","\-500"
"AUD/JPY","売","決済","1","92.457","-300","0","-300","2022/10/17 09:11:45","2022/10/17 09:11:45","126000006431663","-","0","\-300"
"AUD/JPY","買","決済","1","92.453","110","0","110","2022/10/17 09:41:51","2022/10/17 09:41:51","126000006432146","-","0","\110"
"AUD/JPY","買","決済","1","92.57","-2,210","0","-2,210","2022/10/17 10:32:06","2022/10/17 10:32:06","126000006433108","-","0","\-2,210"
"AUD/JPY","買","決済","1","92.62","-870","0","-870","2022/10/17 10:56:44","2022/10/17 10:56:44","126000006433377","-","0","\-870"
"AUD/JPY","売","決済","1","92.707","930","0","930","2022/10/17 15:46:17","2022/10/17 15:46:17","126000006437788","-","0","\930"
"AUD/JPY","買","決済","1","92.687","110","0","110","2022/10/17 15:54:43","2022/10/17 15:54:43","126000006437950","-","0","\110"
"AUD/JPY","売","決済","1","92.822","150","0","150","2022/10/17 16:02:27","2022/10/17 16:02:27","126000006438163","-","0","\150"
"AUD/JPY","売","決済","1","92.874","20","0","20","2022/10/17 16:04:09","2022/10/17 16:04:09","126000006438259","-","0","\20"
"AUD/JPY","買","決済","1","92.882","-20","0","-20","2022/10/17 16:05:22","2022/10/17 16:05:22","126000006438307","-","0","\-20"
"AUD/JPY","買","決済","1","92.878","-280","0","-280","2022/10/17 16:06:39","2022/10/17 16:06:39","126000006438367","-","0","\-280"
"AUD/JPY","売","決済","1","92.733","-1,430","0","-1,430","2022/10/17 16:22:01","2022/10/17 16:22:01","126000006438824","-","0","\-1,430"

重複した行がなくなりました。

ここまで、まじめに読まれた方は上記の入力コマンドについては理解できていると思いますが、復習の意味も込めて入力したコマンドについて解説します。

cat OnlyKessaiS.csv cat は、Get-Content のエイリアスです。ファイル OnlyKessaiS.csv の内容を Object形式で出力します。

| gu gu は、Get-Unique のエイリアスです。パイプラインで　Get-Content の出力を受け取ります。Get-Unique は、重複した項目を排除します。

| oss oss は、Out-String -Stream の省略形です。パイプラインで Get-Unique の出力を受け取ります。受け取ったデータは Object　です。Out-String -Stream は、受け取った Object を String に変換して Stream に出力します。

| sls JPY sls は、Select-String のエイリアスです。”JPY” にマッチする行だけを出力します。

この結果を KessaiFinal.csv というファイルに書き出します。

PS C:\sumbyday> cat OnlyKessaiS.csv | gu | oss | sls JPY | out-file KessaiFinal.csv
PS C:\sumbyday> cat KessaiFinal.csv

"AUD/JPY","買","決済","1","92.538","-500","0","-500","2022/10/17 09:07:30","2022/10/17 09:07:30","126000006431541","-","0","\-500"
"AUD/JPY","売","決済","1","92.457","-300","0","-300","2022/10/17 09:11:45","2022/10/17 09:11:45","126000006431663","-","0","\-300"
"AUD/JPY","買","決済","1","92.453","110","0","110","2022/10/17 09:41:51","2022/10/17 09:41:51","126000006432146","-","0","\110"
"AUD/JPY","買","決済","1","92.57","-2,210","0","-2,210","2022/10/17 10:32:06","2022/10/17 10:32:06","126000006433108","-","0","\-2,210"
"AUD/JPY","買","決済","1","92.62","-870","0","-870","2022/10/17 10:56:44","2022/10/17 10:56:44","126000006433377","-","0","\-870"
"AUD/JPY","売","決済","1","92.707","930","0","930","2022/10/17 15:46:17","2022/10/17 15:46:17","126000006437788","-","0","\930"
"AUD/JPY","買","決済","1","92.687","110","0","110","2022/10/17 15:54:43","2022/10/17 15:54:43","126000006437950","-","0","\110"
"AUD/JPY","売","決済","1","92.822","150","0","150","2022/10/17 16:02:27","2022/10/17 16:02:27","126000006438163","-","0","\150"
"AUD/JPY","売","決済","1","92.874","20","0","20","2022/10/17 16:04:09","2022/10/17 16:04:09","126000006438259","-","0","\20"
"AUD/JPY","買","決済","1","92.882","-20","0","-20","2022/10/17 16:05:22","2022/10/17 16:05:22","126000006438307","-","0","\-20"
"AUD/JPY","買","決済","1","92.878","-280","0","-280","2022/10/17 16:06:39","2022/10/17 16:06:39","126000006438367","-","0","\-280"
"AUD/JPY","売","決済","1","92.733","-1,430","0","-1,430","2022/10/17 16:22:01","2022/10/17 16:22:01","126000006438824","-","0","\-1,430"

次に行いたいのは、決済損益額を間にカンマ[,]を入れて横に並べたいことです。これは、棒グラフのデータになります。

Bar Chart（棒グラフ）のデータ

棒グラフのデータは、決済損益額を横並びにしたものになります。

CSVファイルの8項目（決済損益額）を横並びにする

取り敢えず、CSVファイルをインポートしてから、決済損益額のみを抜き出します。

Import-Csv KessaiFinal.csv -Header H1,H2,H3,H4,H5,H6 | Select-Object H6

PS C:\sumbyday> Import-Csv KessaiFinal.csv -Header H1,H2,H3,H4,H5,H6 | Select-Object H6

H6
--
-500
-300
110
-2,210
-870
930
110
150
20
-20
-280
-1,430

1行目の H6、２行目の — は要らないので、省きます。

Import-Csv KessaiFinal.csv -Header H1,H2,H3,H4,H5,H6 | Select-Object H6 | oss | sls “H6″,”–” -NotMatch

PS C:\sumbyday> Import-Csv KessaiFinal.csv -Header H1,H2,H3,H4,H5,H6 | Select-Object H6 | oss | sls "H6","--" -NotMatch

-500
-300
110
-2,210
-870
930
110
150
20
-20
-280
-1,430

結果を SonEki.txt としてファイルに書き出します。

PS C:\sumbyday> Import-Csv KessaiFinal.csv -Header H1,H2,H3,H4,H5,H6 | Select-Object H6 | oss | sls "H6","--" -NotMatch | out-file SonEki.txt
PS C:\sumbyday> cat SonEki.txt

-500
-300
110
-2,210
-870
930
110
150
20
-20
-280
-1,430

このデータを横並びにして間に[,]を入れて行きます。その際に千円以上の金額に入っているカンマを排除して行きます。

% ForEach-Object , 正規表現 Replace演算子　

千円以上の決済損益額には、カンマが入っています。このカンマを排除します。

cat SonEki.txt | % { $_.Replace(“,”,””)}

% は ForEach-Object のエイリアスです。

PS C:\sumbyday> Get-Alias %
CommandType     Name                                               Version    Source
-----------     ----                                               -------    ------
Alias           % -> ForEach-Object

$_ には、cat SonEki.txt の出力データが渡されます。Replace は正規表現のReplace演算子です。 Replace(“,”,””) で与えられたデータの中のカンマ “,” をNull “” に変換します。この作業を１行づつ繰り返し行います。

PS C:\sumbyday> cat SonEki.txt | % { $_.Replace(",","")}


-500
-300
110
-2210
-870
930
110
150
20
-20
-280
-1430

金額内の “,” が排除されました。

文字列から数値（整数）への変換　[INT]

マッチングなしでメッセージ可能ハッピーメール(18禁)

前述の Replace演算子を使わないで文字列を整数に変換する [INT] を使っても金額表現のカンマを排除できます。

cat SonEki.txt | % { [INT]$_ }

PS C:\sumbyday>  cat SonEki.txt | % { [INT]$_ }
0
0
-500
-300
110
-2210
-870
930
110
150
20
-20
-280
-1430
0
0
0
0

しかし、この場合パイプラインで渡された後の処理が文字列としての扱いである場合は、OSS(Out-String -Stream) を通す必要があります。また、改行だけの行も数値の０に変換されてしまいます。

次は、このデータを横に並べて間にカンマ “,” を入れて行きます。カンマの排除は replace演算子を使うこととします。

cat SonEki.txt | % { $_.Replace(“,”,””)} | % { $BarData = $BarData + $_ + “,” }

$BarData という変数に $_ とカンマ “,” を追記して行きます。

PS C:\sumbyday> cat SonEki.txt | % { $_.Replace(",","")} | % { $BarData = $BarData + $_ + "," }
PS C:\sumbyday> $BarData
,,-500  ,-300  ,110   ,-2210,-870  ,930   ,110   ,150   ,20    ,-20   ,-280  ,-1430,,,,,

横並びに出来ましたが、前と後ろに余分なカンマが入っています。これは元のファイルに改行だけの行があるためです。

? Where-Object , 数値判定の正規表現　, 正規表現 -Match 演算子

老舗サイトのラブサーチ[18歳以上]

行のデータが数値かどうかを判定して、数値の場合だけ出力するようにします。

cat SonEki.txt | % { $_.Replace(“,”,””)} | ? { $_ -Match “[0-9]{2}” -Or $_ -Match “0” }

$_ は入力されたデータです。 “[0-9]{2}” は、データに 0-9 の数値が 2桁以上含まれているか　の正規表現です。(追記：金額が 0　の場合スキップされてしまうので -Or $_ -Match “0” を足して数値0の場合も処理するように修正)

? は Where-Object のエイリアスです。 ? { $_ -Match “[0-9]{2}” } で入力データに２桁以上の 0-9 の数値が含まれていれば、そのデータを出力します。そうでない場合は出力しません。

cat SonEki.txt | % { $_.Replace(“,”,””)}　　の時は、以下の出力でした。

PS C:\sumbyday> cat SonEki.txt | % { $_.Replace(",","")}


-500
-300
110
-2210
-870
930
110
150
20
-20
-280
-1430

データの上下に空白行があります。

cat SonEki.txt | % { $_.Replace(“,”,””)} | ? { $_ -Match “[0-9]{2}” -Or $_ -Match “0” }　の出力は以下になります。

PS C:\sumbyday> cat SonEki.txt | % { $_.Replace(",","")}  | ? { $_ -Match "[0-9]{2}"  -Or $_ -Match "0"}
-500
-300
110
-2210
-870
930
110
150
20
-20
-280
-1430

空白行が出なくなりました。

このデータを横並びにします。

cat SonEki.txt | % { $_.Replace(“,”,””)} | ? { $_ -Match “[0-9]{2}” -Or $_ -Match “0”} | % { $BarData = $BarData + $_ + “,” }

尚、一度　$BarData という変数使っていますので、すでに前のデータが入っています。再実行の場合は必ず $BarData = “” と入れて　$BarData 変数を初期化しておきます。

PS C:\sumbyday> $BarData = ""
PS C:\sumbyday> cat SonEki.txt | % { $_.Replace(",","")}  | ? { $_ -Match "[0-9]{2}"  -Or $_ -Match "0"} | % { $BarData = $BarData + $_ + "," }
PS C:\sumbyday> $BarData
-500  ,-300  ,110   ,-2210,-870  ,930   ,110   ,150   ,20    ,-20   ,-280  ,-1430,

期待通りのデータが出来上がりました。このデータは、Bar Chart の　データになります。ファイルに書き出しておきます。

＄BarData | Out-File BarChartData.txt

PS C:\sumbyday> $BarData | Out-File BarChartData.txt
PS C:\sumbyday> cat BarChartData.txt
-500  ,-300  ,110   ,-2210,-870  ,930   ,110   ,150   ,20    ,-20   ,-280  ,-1430,

Bar Chart データ作成までのまとめ

ここで、Bar Chart のデータが出来上がるまでのステップをまとめておきます。

CSVファイルから決済約定だけを抜き出し、OnlyKessai.csv というファイルにします。

sls -Pattern “決済” test.csv | select Line | oss | sls JPY | Out-File OnlyKessai.csv

または

cat test.csv | sls “決済” | sls JPY | Out-File OnlyKessai.csv

OnlyKessai.csv の重複行を排除するため、先ず約定日時でソートして、OnlyKessaiS.csv というファイルにします。

Import-Csv OnlyKessai.csv -Header H1,H2,H3,H4,H5,H6,H7,H8,H9,H10 | Sort-Object H10 | Export-Csv OnlyKessaiS.csv -Encoding UTF8

OnlyKessaiS.csv から重複行を排除して KessaiFinal.csv というファイルにします。

cat OnlyKessaiS.csv | gu | oss | sls JPY | out-file KessaiFinal.csv

KessaiFinal.csv から決済約定金額だけを抜き出して、SonEki.txt というファイルにします。

Import-Csv KessaiFinal.csv -Header H1,H2,H3,H4,H5,H6 | Select-Object H6 | oss | sls “H6″,”–” -NotMatch | out-file SonEki.txt

SonEki.txt のデータで千円以上の金額に入っているカンマを削除して、数値のみの時のデータを間にカンマを入れながら横ならびにします。

cat SonEki.txt | % { $_.Replace(“,”,””)} | ? { $_ -Match “[0-9]{2}” -Or $_ -Match “0” } | % { $BarData = $BarData + $_ + “,” }

横並びにしたデータを BarChartData.txt というファイルに書き出します。

$BarData | Out-File BarChartData.txt

以上がBar Chart(折れ線グラフ）のデータ作成までのステップです。

Line Chart（折れ線グラフ）のデータ

イククルはコチラ!（18禁）

前項までで出来上がった横並びのデータは、Bar Chart（棒グラフ）のデータになります。Line Chart(折れ線グラフ）のデータは棒グラフのデータを加算していった値となります。次からはパイプラインの他にスクリプトの実行が不可欠となりますので、先ず、スクリプトの実行ポリシーについてご説明します。

スクリプトの実行ポリシー　Set-ExecutionPolicy

次項からコマンドラインにFunction を書いて、それを実行するということを行います。一々　長ったらしい Function をコマンドラインに打ち込むのは大変な手間です。PowerShell では、コマンドラインからの実行の他に　スクリプトを記述し、そのスクリプト・ファイルを実行するという方法があります。

スクリプトは、予め決められたポリシーにより実行が許可・不許可となります。最初は、ディフォルトでRestrictedとなっており、すべてのスクリプトは実行できません。（以下）

PS C:\sumbyday> .\test.ps1
.\test.ps1 : このシステムではスクリプトの実行が無効になっているため、ファイル C:\sumbyday\test.ps1 を読み込むことができません。詳細については、「about_Execution_Policies」(https://go.microsoft.com/fwlink
/?LinkID=135170) を参照してください。
発生場所 行:1 文字:1
+ .\test.ps1
+ ~~~~~~~~~~
    + CategoryInfo          : セキュリティ エラー: (: ) []、PSSecurityException
    + FullyQualifiedErrorId : UnauthorizedAccess

実行ポリシーを Set-ExecutionPolicy によって設定し直すことで、スクリプトの実行が可能となります。

Set-ExecutionPolicy を実行するためには、PowerShell を管理者モードで立ち上げる必要があります。PowerShellを右クリックして出てきた　管理者として実行する　をクリックし PowerShell を管理者モードで立ち上げます。

実行ポリシーには、いろいろとありますが、 RemoteSigned あたりにしておけば良いと思います。リモートでサインされたスクリプトとローカルにあるスクリプトが実行可能となります。

管理者モードで立ち上げた PowerShell のウィンドウで Set-ExecutionPolicy RemoteSigned とキーインし、出てきたプロンプトに Y を返します。

PS C:\sumbyday> Set-ExecutionPolicy RemoteSigned

実行ポリシーの変更
実行ポリシーは、信頼されていないスクリプトからの保護に役立ちます。実行ポリシーを変更すると、about_Execution_Policies のヘルプ トピック (https://go.microsoft.com/fwlink/?LinkID=135170)
で説明されているセキュリティ上の危険にさらされる可能性があります。実行ポリシーを変更しますか?
[Y] はい(Y)  [A] すべて続行(A)  [N] いいえ(N)  [L] すべて無視(L)  [S] 中断(S)  [?] ヘルプ (既定値は "N"): y

これでスクリプトが実行可能となりました。

損益額を加算するFunction 作成　スクリプト記述

マッチングなしでメッセージ可能ハッピーメール(18禁)

それでは、Line Chart のデータを前項で作成した SonEki.txt から作って行きましょう。今までは、パイプラインの活用でなんとか処理できたのですが、ここからはパイプラインだけではちょっと難しくなってきましたので、次の様な Function を書いてみました。

PS C:\sumbyday> Function SumLine {
>>             Begin {
>>                $LCD = 0
>>             }
>>            Process  { % { $LCD = $LCD + [INT]$_  } {  Write-OutPut $LCD
>>                       }
>>             }
>>  }
PS C:\sumbyday> cat SonEki.txt | ? { $_ -Match "[0-9]{2}" -Or $_ -Match "0"} | Sumline
-500
-800
-690
-2900
-3770
-2840
-2730
-2580
-2560
-2580
-2860
-4290

Begin {} は、パイプラインで渡されるプロセスの最初だけに処理されます。 Process {} は毎回処理されます。ForEach で $LCD = $LCD + [INT]$_ により、パイプラインの入力を整数型に変換して $LCD に加算して行きます。 Write-OutPut $LCD で毎回 $LCD をstream に書き出します。

期待通りの出力を得られました。

KasanGaku.txt というファイルに書き出します。

cat SonEki.txt | ? { $_ -Match “[0-9]{2}” -Or $_ -Match “0”} | Sumline | Out-file KasanGaku.txt

PS C:\sumbyday> cat SonEki.txt | ? { $_ -Match "[0-9]{2}" -Or $_ -Match "0"} | Sumline | Out-file KasanGaku.txt
PS C:\sumbyday> cat Kasangaku.txt
-500
-800
-690
-2900
-3770
-2840
-2730
-2580
-2560
-2580
-2860
-4290

一連のコマンドをスクリプトに記述し、スクリプトを実行してみましょう。

function sumline {
Begin {
               $LCD = 0
            }
            Process  { % { $LCD = $LCD + [INT]$_  } {  Write-OutPut $LCD
                      }
            }
 }
cat SonEki.txt | ? { $_ -Match "[0-9]{2}" -Or $_ -Match "0"} | Sumline | Out-file KasanGaku.txt

KasanGaku.ps1 というスクリプト・ファイルを作りました。

実行してみます。スクリプトの実行には、ファイル名の他にパスを明示する必要があります。カレント・ディレクトリでの作業では .\ を付ける必要があります。

PS C:\sumbyday> .\KasanGaku.ps1
PS C:\sumbyday> ls Ka*


    ディレクトリ: C:\sumbyday


Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
-a----        2022/11/05     15:23            265 KasanGaku.ps1
-a----        2022/11/05     15:23            164 KasanGaku.txt


PS C:\sumbyday> cat KasanGaku.txt
-500
-800
-690
-2900
-3770
-2840
-2730
-2580
-2560
-2580
-2860
-4290

加算額を横並びにして Line Chartのデータ作成

老舗サイトのラブサーチ[18歳以上]

KasanGaku.txt のデータを横に並べたものが、Line Chart のデータになります。

Bar Chart のデータを作ったのと同じように、KasanGaku.txt のデータを横並びにして間にカンマを入れて行きます。

Cat KasanGaku.txt | % { $LineChartD = $LineChartD + $_ + “,” }

PS C:\sumbyday> Cat KasanGaku.txt | % { $LineChartD = $LineChartD + $_ + "," }
PS C:\sumbyday> $LineChartD
-500,-800,-690,-2900,-3770,-2840,-2730,-2580,-2560,-2580,-2860,-4290,

LineChartData.txt というファイルに書き出します。

$lineChartD | Out-File LineChartData.txt

PS C:\sumbyday> $lineChartD | Out-File LineChartData.txt
PS C:\sumbyday> cat LineChartData.txt
-500,-800,-690,-2900,-3770,-2840,-2730,-2580,-2560,-2580,-2860,-4290,

グラフのLabelを作成

Bar Chart のデータも、Line Chart のデータも出来ました。次はグラフのLabelです。

Label は、約定日時になります。

先ず、KessaiFinal.csv から約定日時だけを抽出します。

Import-Csv KessaiFinal.csv -header H1,H2,H3,H4,H5,H6,H7,H8,H9,H10 | Select-Object H10 | OSS | ? { $_ -Match “[0-9]{4}” }

PS C:\sumbyday> Import-Csv KessaiFinal.csv -header h1,h2,h3,h4,h5,h6,h7,h8,h9,h10 | Select-Object h10 | oss | ? {$_ -Match "[0-9]{4}"}
2022/10/17 09:07:30
2022/10/17 09:11:45
2022/10/17 09:41:51
2022/10/17 10:32:06
2022/10/17 10:56:44
2022/10/17 15:46:17
2022/10/17 15:54:43
2022/10/17 16:02:27
2022/10/17 16:04:09
2022/10/17 16:05:22
2022/10/17 16:06:39
2022/10/17 16:22:01

KessaiFinal.csv の項目１０番目に約定日時が入っています。

Import-Csv KessaiFinal.csv -header h1,h2,h3,h4,h5,h6,h7,h8,h9,h10

Import-Csv で header １０番目までを入力します。数値４桁がある行だけを出力しています。

Select-Object H10　　Header 10 だけを選択して出力します。

oss | ? {$_ -Match “[0-9]{4}”}　　Object の入力をストリングとして出力し、数値４桁にマッチする行だけを出力します。

結果を YakuteiDate.txt というファイルに書き出します。

Import-Csv KessaiFinal.csv -header h1,h2,h3,h4,h5,h6,h7,h8,h9,h10 | Select-Object h10 | oss | ? {$_ -Match “[0-9]{4}”} | Out-File YakuteiDate.txt

Substring 演算子　

イククルはコチラ!（18禁）

必要なのは、約定日時の中の日付だけです。次の式で日付の部分だけ抽出します。

cat YakuteiDate.txt | % { $_.Substring(5,5) }

PS C:\sumbyday\aug> cat YakuteiDate.txt | % { $_.Substring(5,5) }
08/08
08/08
08/08
08/08
08/08
08/08
08/08
08/08
08/08
08/08
08/08
08/08
08/08
08/08
08/08
08/08
08/08
08/08
08/09
08/09
08/09
08/09
08/09
08/09
08/09

$_.Substring(5,5) 　文字列の5桁目から５文字だけ抜き出します。

この文字列をシングル・クォーテーションで囲って、間にカンマを付けながら横に並べればグラフのLabel Data になります。

cat YakuteiDate.txt | % { $_.Substring(5,5) } | % { $Label = $Label + “‘” + $_ + “‘” + “,” }

PS C:\sumbyday\aug> $label = ""
PS C:\sumbyday\aug> cat YakuteiDate.txt | % { $_.Substring(5,5) } | % { $Label = $Label + "'" + $_ + "'" + "," }
PS C:\sumbyday\aug> $Label
'08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/10','08/10','08/10','08/10','08/10','08/10','08/10','08/12','08/15','08/23','08/23','08/23','08/23','08/23','08/23','08/23','08/23','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/27','08/27','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/31','08/31','08/31','08/31',

LabelData.txt というファイルに書き出します。

$Label | Out-File LabelData.txt

PS C:\sumbyday\aug> $Label | Out-File LabelData.txt
PS C:\sumbyday\aug> ls label*.txt
    ディレクトリ: C:\sumbyday\aug
Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
-a----        2022/11/06     17:02           2550 LabelData.txt

PS C:\sumbyday\aug> cat LabelData.txt
'08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/10','08/10','08/10','08/10','08/10','08/10','08/10','08/12','08/15','08/23','08/23','08/23','08/23','08/23','08/23','08/23','08/23','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/27','08/27','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/31','08/31','08/31','08/31',

グラフのLabel data が出来上がりました。

Chart HTML テンプレートにグラフ・データを挿入

前項までで、Bar Chart(棒グラフ)のデータ、Line Chart(折れ線グラフ)のデータ、グラフの Label とすべて揃いました。ここからはこのデータをグラフ描画のHTMLテンプレートに入れて行く作業を PowerShell で実現して行きます。その前に、これらのデータで実際にどのようなグラフが出来るのか見てみたいと思い、取り敢えず８月の約定履歴からデータを作ってマニュアルでHTMLテンプレートにアップデートしてみました。

<!doctype html>
<html>
    <head>
        <title>Chart Sample</title>
        <link rel="stylesheet" href="Chart.css">
        <script src="\Windows\System32\node_modules\chart.js\dist\Chart.js"></script>
    </head>
    <body> 
    <div id="wrapper">
    <canvas id="myChart" width="" height=""></canvas>
    </div>
<script>
var canvas = document.getElementById('myChart');
   canvas.width =  wrapper.offsetWidth;
   canvas.height = wrapper.offsetHeight;
const ctx = document.getElementById('myChart').getContext('2d');
const myChart = new Chart(ctx, {
    
    data: {
        //xxxLABEL 
        labels: [ '08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/10','08/10','08/10','08/10','08/10','08/10','08/10','08/12','08/15','08/23','08/23','08/23','08/23','08/23','08/23','08/23','08/23','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/27','08/27','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/31','08/31','08/31','08/31'],
        datasets: [{
                  type: 'line',
            label: '損益合計',
            //xxxLine Data
            data: [ 80,160,270,370,300,300,420,630,730,1180,1170,1210,1360,1390,1420,1480,1490,1520,1610,2120,2480,2460,2510,2740,2970,2670,2730,4020,4120,5110,5990,6380,6630,6920,6930,6990,7010,7010,7040,7040,7120,7130,6890,7140,7230,7300,7010,7030,6990,6480,6560,6650,7910,7720,7760,7940,8230,8750,9060,9580,3800,1550,-1620,-660,-1730,-1470,-1370,-960,-730,-580,-230,-1550,-1420,-840,-340,1300,960,500,70,-390,-1490,-2450,-3470,-3460,-3440,-3130,-3030,-2980,-3560,-3510,-3270,-3320,-3380,-3070,-3450,-4160,-4070,-3880,-3790,-3810,-3760,-3720,-3650,-3630,-3620,-3650,-3340,-3320,-3170,-2530,-2080,-180,1020,2220,980,1230,1250,1200,2200,2190,2280,1970,3070,3250,2230,3760,4760,5050,5650,4640,3500,2890,2990,3130,2900,5190,4490,4540,4670,4710,4710,4770,4170,5360,5470,6060,6110,6240,5340,5850,6210,6280,4220,4230,11810,10810,9790,8750,7840, ],
           borderColor: [
                'rgba(255, 206, 86, 1)']
           
        },{
             type: 'bar',
            label: '損益/日',
            //xxxBar Data
            data: [ 80    ,80    ,110   ,100   ,-70   ,0     ,120   ,210   ,100   ,450   ,-10   ,40    ,150   ,30    ,30    ,60    ,10    ,30    ,90    ,510   ,360   ,-20   ,50    ,230   ,230   ,-300  ,60    ,1290 ,100   ,990   ,880   ,390   ,250   ,290   ,10    ,60    ,20    ,0     ,30    ,0     ,80    ,10    ,-240  ,250   ,90    ,70    ,-290  ,20    ,-40   ,-510  ,80    ,90    ,1260 ,-190  ,40    ,180   ,290   ,520   ,310   ,520   ,-5780,-2250,-3170,960   ,-1070,260   ,100   ,410   ,230   ,150   ,350   ,-1320,130   ,580   ,500   ,1640 ,-340  ,-460  ,-430  ,-460  ,-1100,-960  ,-1020,10    ,20    ,310   ,100   ,50    ,-580  ,50    ,240   ,-50   ,-60   ,310   ,-380  ,-710  ,90    ,190   ,90    ,-20   ,50    ,40    ,70    ,20    ,10    ,-30   ,310   ,20    ,150   ,640   ,450   ,1900 ,1200 ,1200 ,-1240,250   ,20    ,-50   ,1000 ,-10   ,90    ,-310  ,1100 ,180   ,-1020,1530 ,1000 ,290   ,600   ,-1010,-1140,-610  ,100   ,140   ,-230  ,2290 ,-700  ,50    ,130   ,40    ,0     ,60    ,-600  ,1190 ,110   ,590   ,50    ,130   ,-900  ,510   ,360   ,70    ,-2060,10    ,7580 ,-1000,-1020,-1040,-910  ,],
            backgroundColor: [
                'rgba(200, 99, 132, 1)']        }]
    }, 
    options: {
        scales: {
            y: {
                beginAtZero: true
            }
        }
    }
});
</script>
</body>
</html>

以下が実行結果です。

想定通りのグラフが描かれていると思います。

スクリプトでHTMLファイルを書いて行く

プログラミングのすすめ　第七歩の VBでは、HTMLファイル内のコメントを探して、コメントのラインをデータで入れ替えるという作業をしました。今回のPowerShell では、HTMLファイルを一からスクリプトで書いて行き、グラフのLabel・Line Chart のデータ・Bar Chart のデータの部分は、今回作成したデータのファイルから挿入する　という方法でHTMLファイルを作って行きます。

ファイルへのAppend Add-Content

老舗サイトのラブサーチ[18歳以上]

既にあるHTMLテンプレートの一行づつをファイルに書いて行きます。

White-OutPut ‘ HTMLファイルの行 ‘ | Add-Content NewHTMLファイル名

行の頭に Write-OutPut を付けて、行をシングルクォーテーション ‘ で囲みます。その後にパイプライン | と Add-Content ファイル名　を付け加えて行きます。

最初の20行目までは、以下のようになります。

$FileName = ".\MixedChartOfTrade.HTML"
Write-OutPut '<!doctype html>'                                                                         | Add-Content $FileName
Write-OutPut '<html>'                                                                                  | Add-Content $FileName
Write-OutPut '    <head>'                                                                              | Add-Content $FileName
Write-OutPut '        <title>Chart Sample</title>'                                                     | Add-Content $FileName
Write-OutPut '        <link rel="stylesheet" href="Chart.css">'                                        | Add-Content $FileName
Write-OutPut '        <script src="\Windows\System32\node_modules\chart.js\dist\Chart.js"></script>'   | Add-Content $FileName
Write-OutPut '    </head>'                                                                             | Add-Content $FileName
Write-OutPut '    <body> '                                                                             | Add-Content $FileName
Write-OutPut '    <div id="wrapper">'                                                                  | Add-Content $FileName
Write-OutPut '    <canvas id="myChart" width="" height=""></canvas>'                                   | Add-Content $FileName
Write-OutPut '    </div>'                                                                              | Add-Content $FileName
Write-OutPut '<script>'                                                                                | Add-Content $FileName
Write-OutPut 'var canvas = document.getElementById(''myChart'');'                                      | Add-Content $FileName
Write-OutPut '   canvas.width =  wrapper.offsetWidth;'                                                 | Add-Content $FileName
Write-OutPut '   canvas.height = wrapper.offsetHeight;'                                                | Add-Content $FileName
Write-OutPut 'const ctx = document.getElementById(''myChart'').getContext(''2d'');'                    | Add-Content $FileName
Write-OutPut 'const myChart = new Chart(ctx, {'                                                        | Add-Content $FileName
Write-OutPut '    data: {'                                                                             | Add-Content $FileName
Write-OutPut '        //xxxLABEL '                                                                     | Add-Content $FileName
Write-OutPut '        labels: [ '                                                                      | Add-Content $FileName

14,17行目のようにテキスト中にシングルクォーテーションが存在する場合は、シングルクォーテーションを二つ続けて記述します。MakeHTMLtest.ps1 を実行してみます。

PS C:\sumbyday\aug> .\MakeHTMLtest.ps1
PS C:\sumbyday\aug> cat MixedChartOfTrade.HTML
<!doctype html>
<html>
    <head>
        <title>Chart Sample</title>
        <link rel="stylesheet" href="Chart.css">
        <script src="\Windows\System32\node_modules\chart.js\dist\Chart.js"></script>
    </head>
    <body>
    <div id="wrapper">
    <canvas id="myChart" width="" height=""></canvas>
    </div>
<script>
var canvas = document.getElementById('myChart');
   canvas.width =  wrapper.offsetWidth;
   canvas.height = wrapper.offsetHeight;
const ctx = document.getElementById('myChart').getContext('2d');
const myChart = new Chart(ctx, {
    data: {
        //xxxLABEL
        labels: [

正しくHTMLのテキストが書き出されています。

次の行は、グラフのLabelが入りますので、cat LabelData.txt | Add-Content $FileName と記述して Label データをファイルに書き出します。LineChartData.txt と BarChartData.txt も同じように該当の行に書き出します。

Write-OutPut '        //xxxLABEL '                                                                     | Add-Content $FileName
Write-OutPut '        labels: [ '                                                                      | Add-Content $FileName
cat LabelData.txt                                                                                      | Add-Content $FileName  
Write-OutPut '],'                                                                                      | Add-Content $FileName 
Write-OutPut '        datasets: [{'                                                                    | Add-Content $FileName
Write-OutPut '                  type: ''line'','                                                       | Add-Content $FileName
Write-OutPut '            label: ''損益合計'','                                                        | Add-Content $FileName
Write-OutPut '            //xxxLine Data       '                                                       | Add-Content $FileName
Write-OutPut '            data: [              '                                                       | Add-Content $FileName
cat LineChartData.txt                                                                                  | Add-Content $FileName
Write-OutPut '], '                                                                                     | Add-Content $FileName
Write-OutPut '           borderColor: [         '                                                      | Add-Content $FileName
Write-OutPut '                ''rgba(255, 206, 86, 1)'']'                                              | Add-Content $FileName
Write-OutPut '        },{ '                                                                            | Add-Content $FileName
Write-OutPut '             type: ''bar'','                                                             | Add-Content $FileName
Write-OutPut '            label: ''損益/日'','                                                         | Add-Content $FileName
Write-OutPut '            //xxxBar Data'                                                               | Add-Content $FileName
Write-OutPut '            data: [ '                                                                    | Add-Content $FileName
cat BarChartData.txt                                                                                   | Add-Content $FileName 
Write-OutPut '],'                                                                                      | Add-Content $FileName

ここまでで一度実行してみましょう。その前にテキストはアペンドされるので、前の MixedChartOfTrade.HTML は最初に削除する必要があります。

ファイルの存在を調べる Test-Path, ファイルを削除する Remove-Item　

HTML作成を実行する前にファイルがすでに存在する場合は削除する必要があります。

$FileName = “.\MixedChartOfTrade.HTML”

if (Test-Path $FileName ) {

Remove-Item $FileName

}

$FileName = ".\MixedChartOfTrade.HTML"
if (Test-Path $FileName ) {
  Remove-Item $FileName
}
Write-OutPut '<!doctype html>'                                                                         | Add-Content $FileName
Write-OutPut '<html>'                                                                                  | Add-Content $FileName
Write-OutPut '    <head>'                                                                              | Add-Content $FileName
Write-OutPut '        <title>Chart Sample</title>'                                                     | Add-Content $FileName
~~~~~~~
Write-OutPut '             type: ''bar'','                                                             | Add-Content $FileName
Write-OutPut '            label: ''損益/日'','                                                         | Add-Content $FileName
Write-OutPut '            //xxxBar Data'                                                               | Add-Content $FileName
Write-OutPut '            data: [ '                                                                    | Add-Content $FileName
cat BarChartData.txt                                                                                   | Add-Content $FileName 
Write-OutPut '],'                                                                                      | Add-Content $FileName

実行してみます。

PS C:\sumbyday\aug> .\MakeHTMLtest.ps1
PS C:\sumbyday\aug> cat MixedChartOfTrade.HTML
<!doctype html>
<html>
    <head>
        <title>Chart Sample</title>
        <link rel="stylesheet" href="Chart.css">
        <script src="\Windows\System32\node_modules\chart.js\dist\Chart.js"></script>
    </head>
    <body>
    <div id="wrapper">
    <canvas id="myChart" width="" height=""></canvas>
    </div>
<script>
var canvas = document.getElementById('myChart');
   canvas.width =  wrapper.offsetWidth;
   canvas.height = wrapper.offsetHeight;
const ctx = document.getElementById('myChart').getContext('2d');
const myChart = new Chart(ctx, {
    data: {
        //xxxLABEL
        labels: [
'08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/08','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/09','08/10','08/10','08/10','08/10','08/10','08/10','08/10','08/12','08/15','08/23','08/23','08/23','08/23','08/23','08/23','08/23','08/23','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/24','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/25','08/27','08/27','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/29','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/30','08/31','08/31','08/31','08/31',
],
        datasets: [{
                  type: 'line',
            label: '損益合計',
            //xxxLine Data
            data: [
80,160,270,370,300,300,420,630,730,1180,1170,1210,1360,1390,1420,1480,1490,1520,1610,2120,2480,2460,2510,2740,2970,2670,2730,4020,4120,5110,5990,6380,6630,6920,6930,6990,7010,7010,7040,7040,7120,7130,6890,7140,7230,7300,7010,7030,6990,6480,6560,6650,7910,7720,7760,7940,8230,8750,9060,9580,3800,1550,-1620,-660,-1730,-1470,-1370,-960,-730,-580,-230,-1550,-1420,-840,-340,1300,960,500,70,-390,-1490,-2450,-3470,-3460,-3440,-3130,-3030,-2980,-3560,-3510,-3270,-3320,-3380,-3070,-3450,-4160,-4070,-3880,-3790,-3810,-3760,-3720,-3650,-3630,-3620,-3650,-3340,-3320,-3170,-2530,-2080,-180,1020,2220,980,1230,1250,1200,2200,2190,2280,1970,3070,3250,2230,3760,4760,5050,5650,4640,3500,2890,2990,3130,2900,5190,4490,4540,4670,4710,4710,4770,4170,5360,5470,6060,6110,6240,5340,5850,6210,6280,4220,4230,11810,10810,9790,8750,7840,
],
           borderColor: [
                'rgba(255, 206, 86, 1)']
        },{
             type: 'bar',
            label: '損益/日',
            //xxxBar Data
            data: [
80    ,80    ,110   ,100   ,-70   ,0     ,120   ,210   ,100   ,450   ,-10   ,40    ,150   ,30    ,30    ,60    ,10    ,30    ,90    ,510   ,360   ,-20   ,50    ,230   ,230   ,-300  ,60    ,1290 ,100   ,990   ,880   ,390   ,250   ,290   ,10    ,60    ,20    ,0     ,30    ,0     ,80    ,10    ,-240  ,250   ,90    ,70    ,-290  ,20    ,-40   ,-510  ,80    ,90    ,1260 ,-190  ,40    ,180   ,290   ,520   ,310   ,520   ,-5780,-2250,-3170,960   ,-1070,260   ,100   ,410   ,230   ,150   ,350   ,-1320,130   ,580   ,500   ,1640 ,-340  ,-460  ,-430  ,-460  ,-1100,-960  ,-1020,10    ,20    ,310   ,100   ,50    ,-580  ,50    ,240   ,-50   ,-60   ,310   ,-380  ,-710  ,90    ,190   ,90    ,-20   ,50    ,40    ,70    ,20    ,10    ,-30   ,310   ,20    ,150   ,640   ,450   ,1900 ,1200 ,1200 ,-1240,250   ,20    ,-50   ,1000 ,-10   ,90    ,-310  ,1100 ,180   ,-1020,1530 ,1000 ,290   ,600   ,-1010,-1140,-610  ,100   ,140   ,-230  ,2290 ,-700  ,50    ,130   ,40    ,0     ,60    ,-600  ,1190 ,110   ,590   ,50    ,130   ,-900  ,510   ,360   ,70    ,-2060,10    ,7580 ,-1000,-1020,-1040,-910  ,
],

目的の位置に正しいデータが書き出されています。後の行も同じようにして書き出します。

最後に出来上がった HTML ファイルを表示するために次のコードを記述します。

$FileName | Out-File .\DispChart.ps1
.\DispChart.ps1

DispChart.ps1 というスクリプトファイルに $FileName (‘.\MixedChartOfTrade.HTML’) を書いて、DispChart.ps1 を実行します。

これにより、MixedChartOfTrade.HTML がＷｅｂブラウザで表示されます。

こちらがHTML作成スクリプトの完成形です。ファイル名は、MakeHTML.ps1 としました。

$FileName = ".\MixedChartOfTrade.HTML"
if (Test-Path $FileName ) {
  Remove-Item $FileName
}
Write-OutPut '<!doctype html>'                                                                         | Add-Content $FileName
Write-OutPut '<html>'                                                                                  | Add-Content $FileName
Write-OutPut '    <head>'                                                                              | Add-Content $FileName
Write-OutPut '        <title>Chart Sample</title>'                                                     | Add-Content $FileName
Write-OutPut '        <link rel="stylesheet" href="Chart.css">'                                        | Add-Content $FileName
Write-OutPut '        <script src="\Windows\System32\node_modules\chart.js\dist\Chart.js"></script>'   | Add-Content $FileName
Write-OutPut '    </head>'                                                                             | Add-Content $FileName
Write-OutPut '    <body> '                                                                             | Add-Content $FileName
Write-OutPut '    <div id="wrapper">'                                                                  | Add-Content $FileName
Write-OutPut '    <canvas id="myChart" width="" height=""></canvas>'                                   | Add-Content $FileName
Write-OutPut '    </div>'                                                                              | Add-Content $FileName
Write-OutPut '<script>'                                                                                | Add-Content $FileName
Write-OutPut 'var canvas = document.getElementById(''myChart'');'                                      | Add-Content $FileName
Write-OutPut '   canvas.width =  wrapper.offsetWidth;'                                                 | Add-Content $FileName
Write-OutPut '   canvas.height = wrapper.offsetHeight;'                                                | Add-Content $FileName
Write-OutPut 'const ctx = document.getElementById(''myChart'').getContext(''2d'');'                    | Add-Content $FileName
Write-OutPut 'const myChart = new Chart(ctx, {'                                                        | Add-Content $FileName
Write-OutPut '    data: {'                                                                             | Add-Content $FileName
Write-OutPut '        //xxxLABEL '                                                                     | Add-Content $FileName
Write-OutPut '        labels: [ '                                                                      | Add-Content $FileName
cat LabelData.txt                                                                                      | Add-Content $FileName  
Write-OutPut '],'                                                                                      | Add-Content $FileName 
Write-OutPut '        datasets: [{'                                                                    | Add-Content $FileName
Write-OutPut '                  type: ''line'','                                                       | Add-Content $FileName
Write-OutPut '            label: ''損益合計'','                                                        | Add-Content $FileName
Write-OutPut '            //xxxLine Data       '                                                       | Add-Content $FileName
Write-OutPut '            data: [              '                                                       | Add-Content $FileName
cat LineChartData.txt                                                                                  | Add-Content $FileName
Write-OutPut '], '                                                                                     | Add-Content $FileName
Write-OutPut '           borderColor: [         '                                                      | Add-Content $FileName
Write-OutPut '                ''rgba(255, 206, 86, 1)'']'                                              | Add-Content $FileName
Write-OutPut '        },{ '                                                                            | Add-Content $FileName
Write-OutPut '             type: ''bar'','                                                             | Add-Content $FileName
Write-OutPut '            label: ''損益/日'','                                                         | Add-Content $FileName
Write-OutPut '            //xxxBar Data'                                                               | Add-Content $FileName
Write-OutPut '            data: [ '                                                                    | Add-Content $FileName
cat BarChartData.txt                                                                                   | Add-Content $FileName 
Write-OutPut '],'                                                                                      | Add-Content $FileName
Write-OutPut '            backgroundColor: ['                                                          | Add-Content $FileName
Write-OutPut '                ''rgba(200, 99, 132, 1)'']        }]'                                    | Add-Content $FileName
Write-OutPut '    }, '                                                                                 | Add-Content $FileName
Write-OutPut '    options: {'                                                                          | Add-Content $FileName
Write-OutPut '        scales: {'                                                                       | Add-Content $FileName
Write-OutPut '            y: {   '                                                                     | Add-Content $FileName
Write-OutPut '                beginAtZero: true'                                                       | Add-Content $FileName
Write-OutPut '            }'                                                                           | Add-Content $FileName
Write-OutPut '        }'                                                                               | Add-Content $FileName
Write-OutPut '    }'                                                                                   | Add-Content $FileName
Write-OutPut '});'                                                                                     | Add-Content $FileName
Write-OutPut '</script>'                                                                               | Add-Content $FileName
Write-OutPut '</body>'                                                                                 | Add-Content $FileName
Write-OutPut '</html>'                                                                                 | Add-Content $FileName
$FileName | Out-File .\DispChart.ps1
.\DispChart.ps1

Chart.css の記述

運用実績20年以上の信頼！安心のポイントサイト《ワラウ》イククルはコチラ!（18禁）

HTMLをご覧いただければ分かるように、Chart.css をstyle sheet としてリンクしています。一々、手動でコピーするのは手間ですのでChart.css もスクリプトで書き出すようにします。

HTML作成後、実行するまでの間に次のコードを記述しました。

if (Test-Path '.\Chart.css' ) {
  Remove-Item '.\Chart.css'
}

Write-OutPut '#canvas{'                     | Add-Content .\Chart.css
Write-OutPut '	display: block;'            | Add-Content .\Chart.css
Write-OutPut '}'                            | Add-Content .\Chart.css
Write-OutPut 'html, body, #wrapper{'        | Add-Content .\Chart.css
Write-OutPut '	width: 95%;'                | Add-Content .\Chart.css
Write-OutPut '	height: 95%;'               | Add-Content .\Chart.css
Write-OutPut '}'                            | Add-Content .\Chart.css

$FileName | Out-File .\DispChart.ps1
.\DispChart.ps1

まとめ

これまでの作業を全部まとめて一つのスクリプト・ファイルにしました。約定履歴のデータは、 TradeRecordList*.csv で参照できる状態で、カレント・ディレクトリにあるものとして動きます。

# Make Bar Chart Data

cat TradeRecordList*.csv | sls "決済" | sls JPY | Out-File OnlyKessai.csv
Import-Csv OnlyKessai.csv -Header H1,H2,H3,H4,H5,H6,H7,H8,H9,H10 | Sort-Object H10 | Export-Csv OnlyKessaiS.csv -Encoding UTF8
cat OnlyKessaiS.csv | gu | oss | sls JPY | out-file KessaiFinal.csv
Import-Csv KessaiFinal.csv -Header H1,H2,H3,H4,H5,H6 | Select-Object H6 | oss | sls "H6","--" -NotMatch | out-file SonEki.txt
$BarData = ""
cat SonEki.txt | % { $_.Replace(",","")}  | ? { $_ -Match "[0-9]{2}" -Or $_ -Match "0" } | % { $BarData = $BarData + $_ + "," }
$BarData | Out-File BarChartData.txt

# Make Line Chart Data

function sumline {
Begin {
               $LCD = 0
            }
            Process  { % { $LCD = $LCD + [INT]$_  } {  Write-OutPut $LCD
                      }
            }
 }
Cat SonEki.txt | ? { $_ -Match "[0-9]{2}" -Or $_ -Match "0" } | Sumline | Out-file KasanGaku.txt
$LineChartD = ""
Cat KasanGaku.txt | % { $LineChartD = $LineChartD + $_ + "," }
$lineChartD | Out-File LineChartData.txt

# Make Label Data 

Import-Csv KessaiFinal.csv -header h1,h2,h3,h4,h5,h6,h7,h8,h9,h10 | Select-Object h10 | oss | ? {$_ -Match "[0-9]{4}"} | Out-File YakuteiDate.txt
cat YakuteiDate.txt | % { $_.Substring(5,5) } | % { $Label = $Label + "'" + $_ + "'" + "," }
$Label | Out-File LabelData.txt

# Make Mixed Chart HTML

$FileName = ".\MixedChartOfTrade.HTML"
if (Test-Path $FileName ) {
  Remove-Item $FileName
}
Write-OutPut '<!doctype html>'                                                                         | Add-Content $FileName
Write-OutPut '<html>'                                                                                  | Add-Content $FileName
Write-OutPut '    <head>'                                                                              | Add-Content $FileName
Write-OutPut '        <title>Chart Sample</title>'                                                     | Add-Content $FileName
Write-OutPut '        <link rel="stylesheet" href="Chart.css">'                                        | Add-Content $FileName
Write-OutPut '        <script src="\Windows\System32\node_modules\chart.js\dist\Chart.js"></script>'   | Add-Content $FileName
Write-OutPut '    </head>'                                                                             | Add-Content $FileName
Write-OutPut '    <body> '                                                                             | Add-Content $FileName
Write-OutPut '    <div id="wrapper">'                                                                  | Add-Content $FileName
Write-OutPut '    <canvas id="myChart" width="" height=""></canvas>'                                   | Add-Content $FileName
Write-OutPut '    </div>'                                                                              | Add-Content $FileName
Write-OutPut '<script>'                                                                                | Add-Content $FileName
Write-OutPut 'var canvas = document.getElementById(''myChart'');'                                      | Add-Content $FileName
Write-OutPut '   canvas.width =  wrapper.offsetWidth;'                                                 | Add-Content $FileName
Write-OutPut '   canvas.height = wrapper.offsetHeight;'                                                | Add-Content $FileName
Write-OutPut 'const ctx = document.getElementById(''myChart'').getContext(''2d'');'                    | Add-Content $FileName
Write-OutPut 'const myChart = new Chart(ctx, {'                                                        | Add-Content $FileName
Write-OutPut '    data: {'                                                                             | Add-Content $FileName
Write-OutPut '        //xxxLABEL '                                                                     | Add-Content $FileName
Write-OutPut '        labels: [ '                                                                      | Add-Content $FileName
cat LabelData.txt                                                                                      | Add-Content $FileName  
Write-OutPut '],'                                                                                      | Add-Content $FileName 
Write-OutPut '        datasets: [{'                                                                    | Add-Content $FileName
Write-OutPut '                  type: ''line'','                                                       | Add-Content $FileName
Write-OutPut '            label: ''損益合計'','                                                        | Add-Content $FileName
Write-OutPut '            //xxxLine Data       '                                                       | Add-Content $FileName
Write-OutPut '            data: [              '                                                       | Add-Content $FileName
cat LineChartData.txt                                                                                  | Add-Content $FileName
Write-OutPut '], '                                                                                     | Add-Content $FileName
Write-OutPut '           borderColor: [         '                                                      | Add-Content $FileName
Write-OutPut '                ''rgba(255, 206, 86, 1)'']'                                              | Add-Content $FileName
Write-OutPut '        },{ '                                                                            | Add-Content $FileName
Write-OutPut '             type: ''bar'','                                                             | Add-Content $FileName
Write-OutPut '            label: ''損益/日'','                                                         | Add-Content $FileName
Write-OutPut '            //xxxBar Data'                                                               | Add-Content $FileName
Write-OutPut '            data: [ '                                                                    | Add-Content $FileName
cat BarChartData.txt                                                                                   | Add-Content $FileName 
Write-OutPut '],'                                                                                      | Add-Content $FileName
Write-OutPut '            backgroundColor: ['                                                          | Add-Content $FileName
Write-OutPut '                ''rgba(200, 99, 132, 1)'']        }]'                                    | Add-Content $FileName
Write-OutPut '    }, '                                                                                 | Add-Content $FileName
Write-OutPut '    options: {'                                                                          | Add-Content $FileName
Write-OutPut '        scales: {'                                                                       | Add-Content $FileName
Write-OutPut '            y: {   '                                                                     | Add-Content $FileName
Write-OutPut '                beginAtZero: true'                                                       | Add-Content $FileName
Write-OutPut '            }'                                                                           | Add-Content $FileName
Write-OutPut '        }'                                                                               | Add-Content $FileName
Write-OutPut '    }'                                                                                   | Add-Content $FileName
Write-OutPut '});'                                                                                     | Add-Content $FileName
Write-OutPut '</script>'                                                                               | Add-Content $FileName
Write-OutPut '</body>'                                                                                 | Add-Content $FileName
Write-OutPut '</html>'                                                                                 | Add-Content $FileName

# Write Out Chart.css 

if (Test-Path '.\Chart.css' ) {
  Remove-Item '.\Chart.css'
}

Write-OutPut '#canvas{'                     | Add-Content .\Chart.css
Write-OutPut '	display: block;'            | Add-Content .\Chart.css
Write-OutPut '}'                            | Add-Content .\Chart.css
Write-OutPut 'html, body, #wrapper{'        | Add-Content .\Chart.css
Write-OutPut '	width: 95%;'                | Add-Content .\Chart.css
Write-OutPut '	height: 95%;'               | Add-Content .\Chart.css
Write-OutPut '}'                            | Add-Content .\Chart.css

# Display HTML file

$FileName | Out-File .\DispChart.ps1
.\DispChart.ps1

実際に実行してみます。

８月のデータがあるディレクトリです。

PS C:\sumbyday\aug> ls


    ディレクトリ: C:\sumbyday\aug


Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
d-----        2022/11/08     17:39                stock
-a----        2022/11/08     17:13           8807 FX_Trade_Chart.ps1
-a----        2022/10/27     15:03          20793 TradeRecordList8.csv

実行結果です。

９月のデータがあるディレクトリです。

PS C:\sumbyday\sep> ls
    ディレクトリ: C:\sumbyday\sep
Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
-a----        2022/11/08     17:13           8807 FX_Trade_Chart.ps1
-a----        2022/10/09     21:42          19314 TradeRecordList9.csv

実行結果です。

８－１０月のすべての期間のデータがあるディレクトリです。

PS C:\sumbyday\all_data> ls
    ディレクトリ: C:\sumbyday\all_data
Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
-a----        2022/11/08     17:13           8807 FX_Trade_Chart.ps1
-a----        2022/10/16     22:43          12652 TradeRecordList (10).csv
-a----        2022/10/16     22:44          12605 TradeRecordList (11).csv
-a----        2022/10/16     22:44          12624 TradeRecordList (12).csv
-a----        2022/10/16     22:44          12510 TradeRecordList (13).csv
-a----        2022/10/16     22:44          12637 TradeRecordList (14).csv
-a----        2022/10/16     22:44          12536 TradeRecordList (15).csv
-a----        2022/10/16     22:45          12512 TradeRecordList (16).csv
-a----        2022/10/16     22:45          12513 TradeRecordList (17).csv
-a----        2022/10/16     22:47           7502 TradeRecordList (18).csv
-a----        2022/10/27     14:11           3143 TradeRecordList (8).csv
-a----        2022/10/16     22:43          12633 TradeRecordList (9).csv

実行結果です。

プログラミングとは関係ないFXに関するボヤキです、、、一時は、３万近くプラスだったのですが、見事に３か月近くで -4万円です（笑）　セルフバックで貰った4万2千円が綺麗になくなった(泣) ところで、只今　クーリング中です。いつかリベンジしたいですが。　

最後にファイル・クリーンナップ

最後に途中で作ったファイルをクリーンナップします。次のコードを付け加えます。

# Clean-Up Files

if (Test-Path '.\BarChartData.txt' ) {
  Remove-Item '.\BarChartData.txt'
}
if (Test-Path '.\DispChart.ps1' ) {
  Remove-Item '.\DispChart.ps1'
}
if (Test-Path '.\KasanGaku.txt' ) {
  Remove-Item '.\KasanGaku.txt'
}
if (Test-Path '.\KessaiFinal.csv' ) {
  Remove-Item '.\KessaiFinal.csv'
}
if (Test-Path '.\LabelData.txt' ) {
  Remove-Item '.\LabelData.txt'
}
if (Test-Path '.\LineChartData.txt' ) {
  Remove-Item '.\LineChartData.txt'
}
if (Test-Path '.\OnlyKessai.csv' ) {
  Remove-Item '.\OnlyKessai.csv'
}
if (Test-Path '.\OnlyKessaiS.csv' ) {
  Remove-Item '.\OnlyKessaiS.csv'
}
if (Test-Path '.\SonEki.txt' ) {
  Remove-Item '.\SonEki.txt'
}
if (Test-Path '.\YakuteiDate.txt' ) {
  Remove-Item '.\YakuteiDate.txt'
}

以上、ここまでPowerShell でのテキストデータの操作をいろいろと試して、実際のFX取引データからグラフを作成する手順をご紹介しました。皆さんも是非 A8.ネットに登録して、DMM FX のセルフバックで４万２千円貰いませんか。FXで下手して負けてもトントンです。

老舗サイトのラブサーチ[18歳以上]

イククルはコチラ!（18禁）

マッチングなしでメッセージ可能ハッピーメール(18禁)

PowerShell の始め方

PowerShell で出来ること、使用するコマンドレットの詳細

ヘルプを表示 Get-Help

エイリアスを知る(コマンドレットの省略表記) Get-Alias

パイプライン ” | “ 、オブジェクトとストリングの違い

オブジェクトをストリングに変換 Out-String

文字列の検索(Grep like) Select-String

オブジェクトをリスト・アップ Format-List

オブジェクトを選択 Select-Object

ファイルに書き出す Out-File

CSV形式を読み込む Import-Csv , オブジェクトでソート Sort-Object

CSV形式で書き出し Export-Csv

重複行を排除 Get-Unique

Bar Chart（棒グラフ）のデータ

CSVファイルの8項目（決済損益額）を横並びにする

% ForEach-Object , 正規表現 Replace演算子

文字列から数値（整数）への変換 [INT]

? Where-Object , 数値判定の正規表現 , 正規表現 -Match 演算子

Bar Chart データ作成までのまとめ

Line Chart（折れ線グラフ）のデータ

スクリプトの実行ポリシー Set-ExecutionPolicy

損益額を加算するFunction 作成 スクリプト記述

加算額を横並びにして Line Chartのデータ作成

グラフのLabelを作成

Substring 演算子

Chart HTML テンプレートにグラフ・データを挿入

スクリプトでHTMLファイルを書いて行く

ファイルへのAppend Add-Content

ファイルの存在を調べる Test-Path, ファイルを削除する Remove-Item

Chart.css の記述

まとめ

最後にファイル・クリーンナップ

コメント

パイプライン　” | “　、オブジェクトとストリングの違い

文字列の検索(Grep like)　Select-String

オブジェクトをリスト・アップ　Format-List

オブジェクトを選択　Select-Object

ファイルに書き出す　Out-File

CSV形式を読み込む Import-Csv , オブジェクトでソート　Sort-Object

CSV形式で書き出し　Export-Csv

重複行を排除　Get-Unique

% ForEach-Object , 正規表現 Replace演算子　

文字列から数値（整数）への変換　[INT]

? Where-Object , 数値判定の正規表現　, 正規表現 -Match 演算子

スクリプトの実行ポリシー　Set-ExecutionPolicy

損益額を加算するFunction 作成　スクリプト記述

Substring 演算子　

ファイルの存在を調べる Test-Path, ファイルを削除する Remove-Item　