cartoohのメモ このページをアンテナに追加 RSSフィード

2018-10-08

カテゴリIDのダウンロード,その2

var downloadAsTextFile = function(fileName, content) {
  var a = document.createElement('a');
  a.download = fileName;
  a.href = (window.URL || window.webkitURL).createObjectURL(new Blob([new Uint8Array([0xEF, 0xBB, 0xBF]), content]));
  document.body.appendChild(a);
  a.click();
  document.body.removeChild(a);
};

var downloadAsCSVFile = function(fileName, rows) {
  var content = "";
  for(var i in rows) {
    for (var j = 0, m = rows[i].length; j < m; ++j) content += '"' + ("" + rows[i][j]).replace(/"/g, '""') + '"' + (j !== m ? ',' : '');
    content += '\n';
  }
  downloadAsTextFile(fileName, content);
};

var large = {}
for(var [k, v] of category_list_minus.find(".l_c_name").toArray().map((e) => [e.id, e.innerHTML])) {
  large[k] = v;
}
for(var [k, v] of category_list_plus.find(".l_c_name").toArray().map((e) => [e.id, e.innerHTML])) {
  large[k] = v;
}
var middle = {};
for(var [pid, id, name] of category_list_minus.find(".m_c_name").toArray().map((e) => [e.parentNode.parentNode.id, e.id, e.innerHTML])) {
  middle[pid] = middle[pid]||{};
  middle[pid][id] = name;
}
for(var [pid, id, name] of category_list_plus.find(".m_c_name").toArray().map((e) => [e.parentNode.parentNode.id, e.id, e.innerHTML])) {
  middle[pid] = middle[pid]||{};
  middle[pid][id] = name;
}
var list = [];
for(var x in middle) { 
  for(var y in middle[x]) {
    list.push([x, large[x], y, middle[x][y]]);
  }
}
downloadAsCSVFile("category_list.csv", list);

2018-07-26

query で Pivot したデータの合計値の列を追加

MMULTIで合計値を計算できるが,色々はまったのでめも。

[月別元データ]シートの内容

=query(
  importrange("スプレッドシートのキー", "マージ!B:S"),
  "Select Col12, sum(Col18), sum(Col17) Where Col7 = '●' Group by Col12 Pivot year(Col1), month(Col1)+1 Label sum(Col18) '元本', sum(Col17) '評価額'"
)
<||

元データを引っ張る。
>||
=offset('月別元データ'!A1, 1, 1, counta('月別元データ'!A2:A), counta('月別元データ'!B1:1)) 

転置して,数値化

=TRANSPOSE( arrayformula( offset('月別元データ'!A1, 1, 1, counta('月別元データ'!A2:A), counta('月別元データ'!B1:1)) * 1))

かけ合わせるベクトルを作る

=ARRAYFORMULA(OFFSET('月別元データ'!A1, 1, 1, counta('月別元データ'!A2:A), 1)^0)

かけ合わせて,転置。合計値行を作る

=transpose(MMULT( TRANSPOSE( arrayformula( offset('月別元データ'!A1, 1, 1, counta('月別元データ'!A2:A), counta('月別元データ'!B1:1)) * 1) ), ARRAYFORMULA(OFFSET('月別元データ'!A1, 1, 1, counta('月別元データ'!A2:A), 1)^0)) )

合計値の列を追加

={offset('月別元データ'!A1, 0, 0, counta('月別元データ'!A:A), counta('月別元データ'!1:1)); 
"合計",transpose(MMULT( TRANSPOSE( arrayformula( offset('月別元データ'!A1, 1, 1, counta('月別元データ'!A2:A), counta('月別元データ'!B1:1)) * 1) ), ARRAYFORMULA(OFFSET('月別元データ'!A1, 1, 1, counta('月別元データ'!A2:A), 1)^0)) )}

2018-07-22

年・四半期・月・週の始めの日,終わりの日のデータだけ表示する式

えらい長い式だけど,やっていることはそこまで複雑じゃないはず。

備考録として記載

あるスプレッドシートの「Record」にA列に日時と,その時のデータの一覧があり,

年・四半期・月・週の始めの日,終わりの日のデータだけ残したい場合の式。

それと,ピボットテーブルで,各年,月等でどのようにデータが変わったか見るために,

どのデータが,年・四半期・月・週の始めの日,終わりの日かわかるようにフラグも残す場合


始めのQuery はフラグのあるデータのみを残す。フラグだけ付けるだけならいらない。


ARRAYFORMULA は各列のデータを一つの式にまとめるために使用

Queryでフィルターしないのであれば,列ごとに分けて計算しても問題ないはず。


importrange("スプレッドシートキー", "Record!A:A"), は元の日時の列を参照。


次のif は見出しか否かを判定。見出しは"日時"という文字列ハードコーディング

ここはもう少しスマートに書きたいが。。。


次のif は 年・四半期・月・週の始めの日,終わりの日 を判定している。

判定方法は,別途query で,日付を year, quarter, month でグルーピングし,

このグループの開始日と終了日だけのテーブルを作り,

このテーブルの日付と今の列の日付が一致するか match検索し,

マッチすれば,isna がfalse, 見つからな変えれば true なるので,

それで判定。


ただし,週に関しては query でサポートしてる関数に week number を取得する

関数がなく,スプレッドシートのweeknumの関数を使うため,

一時的にARRAYFORMULA で 日時とweeknum の2列のテーブルを作り,

二列目(Col2)のweeknumの値でグループ化し,始めと終わりの日時を調べている。


それを全部まとめると,こんな感じ。



=Query(
  ARRAYFORMULA(
    {
      importrange("スプレッドシートキー", "Record!A:A"),
      if(
        importrange("スプレッドシートキー", "Record!A:A") = "日時",
        "年の初め",
        if(isna(
          match(
            importrange("スプレッドシートキー", "Record!A:A"),
            query(
              importrange("スプレッドシートキー", "Record!A:A"),
              "select min(Col1) group by year(Col1)"),
            0)),
          "",
          "●") ),

      if(
        importrange("スプレッドシートキー", "Record!A:A") = "日時",
        "年の終わり",
        if(isna(
          match(
            importrange("スプレッドシートキー", "Record!A:A"),
            query(
              importrange("スプレッドシートキー", "Record!A:A"),
              "select max(Col1) group by year(Col1)"),
            0)),
          "",
          "●") ),

      if(
        importrange("スプレッドシートキー", "Record!A:A") = "日時",
        "四半期の初め",
        if(isna(
          match(
            importrange("スプレッドシートキー", "Record!A:A"),
            query(
              importrange("スプレッドシートキー", "Record!A:A"),
              "select min(Col1) group by year(Col1), quarter(Col1)"),
            0)),
          "",
          "●") ),

      if(
        importrange("スプレッドシートキー", "Record!A:A") = "日時",
        "四半期の終わり",
        if(isna(
          match(
            importrange("スプレッドシートキー", "Record!A:A"),
            query(
              importrange("スプレッドシートキー", "Record!A:A"),
              "select max(Col1) group by year(Col1), quarter(Col1)"),
            0)),
          "",
          "●") ),

      if(
        importrange("スプレッドシートキー", "Record!A:A") = "日時",
        "月の初め",
        if(isna(
          match(
            importrange("スプレッドシートキー", "Record!A:A"),
            query(
              importrange("スプレッドシートキー", "Record!A:A"),
              "select min(Col1) group by year(Col1), month(Col1)"),
            0)),
          "",
          "●") ),

      if(
        importrange("スプレッドシートキー", "Record!A:A") = "日時",
        "月の終わり",
        if(isna(
          match(
            importrange("スプレッドシートキー", "Record!A:A"),
            query(
              importrange("スプレッドシートキー", "Record!A:A"),
              "select max(Col1) group by year(Col1), month(Col1)"),
            0)),
          "",
          "●") ),

      if(
        importrange("スプレッドシートキー", "Record!A:A") = "日時",
        "週の初め",
        if(isna(
          match(
            importrange("スプレッドシートキー", "Record!A:A"),
            query(
              ARRAYFORMULA({
                importrange("スプレッドシートキー", "Record!A:A"),
                weeknum(importrange("スプレッドシートキー", "Record!A:A"))
              }),
              "select min(Col1) group by Col2"),
            0)),
          "",
          "●") ),

      if(
        importrange("スプレッドシートキー", "Record!A:A") = "日時",
        "週の終わり",
        if(isna(
          match(
            importrange("スプレッドシートキー", "Record!A:A"),
            query(
              ARRAYFORMULA({
                importrange("スプレッドシートキー", "Record!A:A"),
                weeknum(importrange("スプレッドシートキー", "Record!A:A"))
              }),
              "select max(Col1) group by Col2"),
            0)),
          "",
          "●") ),


      importrange("スプレッドシートキー", "Record!B:I")
    }
  ),
  "Where Col2 <> '' OR Col3 <> '' OR Col4 <> '' OR Col5 <> '' OR Col6 <> '' OR Col7 <> '' OR Col8 <> '' OR Col9<> ''")

2018-07-16

indexデータの取得

Option Explicit

Private Sub testParse()
    Dim url As String
    url = "http://www.morningstar.co.jp/FundData/common/chart/xml/all/MSCI011010.xml"
    
    Application.StatusBar = "Loading: " & url
    Dim xdoc As New DOMDocument60
    xdoc.async = False
    xdoc.Load url
    Application.StatusBar = ""
    
    If (xdoc.parseError.ErrorCode <> 0) Then 'ロード失敗
        Debug.Print xdoc.parseError.reason   'エラー内容を出力
        Exit Sub
    End If
    
    Dim code As IXMLDOMNode
    Set code = xdoc.SelectSingleNode("//morningstarXML/fund/@code")
    Debug.Print code.Text
    
    Dim name As IXMLDOMNode
    Set name = xdoc.SelectSingleNode("//morningstarXML/fund/@name")
    Debug.Print name.Text
    
    Dim period_end As IXMLDOMNode
    Set period_end = xdoc.SelectSingleNode("//morningstarXML/fund/@period_end")
    Debug.Print period_end.Text
    
    '//morningstarXML/fund/@name //morningstarXML/fund/@period_end
    
    Dim days As IXMLDOMNodeList
    Set days = xdoc.SelectNodes("//day")
    Debug.Print days.Length
        
    Dim day As IXMLDOMNode
    For Each day In days
        Dim attr As IXMLDOMNamedNodeMap
        Set attr = day.Attributes
        
        Dim year As String
        year = attr.getNamedItem("year").Text
        
        Dim month As String
        month = attr.getNamedItem("month").Text
        
        Dim value As String
        value = attr.getNamedItem("value").Text
    
        Dim price As String
        price = attr.getNamedItem("price").Text
    
        Dim volume As String
        volume = attr.getNamedItem("volume").Text
    
        Dim return_value As String
        return_value = attr.getNamedItem("return_value").Text
    
        Dim indication As String
        indication = attr.getNamedItem("indication").Text
    
        Dim work_end As String
        work_end = ""
        Dim node As IXMLDOMNode
        Set node = attr.getNamedItem("work_end")
        If Not node Is Nothing Then
            work_end = node.Text
        End If
    Next
End Sub

Private Sub testLoadIndex()
    Dim bk As Workbook
    Dim sht As Worksheet
    
    Set bk = ThisWorkbook
    
    LoadIndex "NYGL00100", bk.Sheets("Sheet48") 'bk.Sheets.Add
End Sub

Public Sub LoadAllIndex()
    Dim shts As Sheets
    Set shts = ThisWorkbook.Sheets
    
    Dim sht As Worksheet
    Set sht = shts("index_list")
    
    Dim area As Range
    Set area = sht.Range("B:B").SpecialCells(xlCellTypeConstants)
    
    Dim cur As Range
    For Each cur In area
        Debug.Print cur.value
        LoadIndex cur.value, shts.Add(after:=shts(shts.Count))
    Next
    
End Sub


Sub LoadIndex(indexCode As String, sht As Worksheet)
    Dim url As String
    url = "http://www.morningstar.co.jp/FundData/common/chart/xml/all/" & indexCode & ".xml"
    
    Application.StatusBar = "Loading: " & url
    Dim xdoc As New DOMDocument60
    xdoc.async = False
    xdoc.Load url
    Debug.Print url
    Application.StatusBar = ""
    
    If (xdoc.parseError.ErrorCode <> 0) Then 'ロード失敗
        MsgBox xdoc.parseError.reason   'エラー内容を出力
        Exit Sub
    End If
    
    Dim code As IXMLDOMNode
    Set code = xdoc.SelectSingleNode("//morningstarXML/fund/@code")
    Debug.Print code.Text
    
    Dim name As IXMLDOMNode
    Set name = xdoc.SelectSingleNode("//morningstarXML/fund/@name")
    Debug.Print name.Text
    Dim indexName As String
    indexName = name.Text
    
    Dim period_end As IXMLDOMNode
    Set period_end = xdoc.SelectSingleNode("//morningstarXML/fund/@period_end")
    Debug.Print period_end.Text
    
    Dim days As IXMLDOMNodeList
    Set days = xdoc.SelectNodes("//day")
    Debug.Print days.Length
    
        
    Application.ScreenUpdating = False
    Dim Calculation As XlCalculation
    Calculation = Application.Calculation
    Application.Calculation = xlCalculationManual
    
    
    sht.UsedRange.Delete xlToLeft
    
    
    Dim cur As Range
    
    Set cur = sht.Range("B2")
    
    cur.Offset(, 0).value = "Code"
    With cur.Offset(, 1).Resize(, 5)
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .MergeCells = True
    End With
    cur.Offset(, 1).value = code.Text
    
    Set cur = cur.Offset(1)
    cur.Offset(, 0).value = "Name"
    With cur.Offset(, 1).Resize(, 5)
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .MergeCells = True
    End With
    cur.Offset(, 1).value = name.Text
    
    Set cur = cur.Offset(1)
    cur.Offset(, 0).value = "Period End"
    With cur.Offset(, 1).Resize(, 5)
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .MergeCells = True
    End With
    cur.Offset(, 1).value = period_end.Text
    
    Set cur = cur.Offset(2)
    cur.Offset(, 0).value = "Date"
    cur.Offset(, 1).value = "Price"
    cur.Offset(, 2).value = "Volume"
    cur.Offset(, 3).value = "Return Value"
    cur.Offset(, 4).value = "Indication"
    cur.Offset(, 5).value = "Work End"
    
    Dim header As Range
    Set header = sht.Range(cur, cur.Offset(, 255).End(xlToLeft))
    
    
    Dim i As Long
    Dim l As Long
    l = days.Length - 1
    For i = 0 To l
        Application.StatusBar = indexName & "; " & i & " / " & l
        
        Dim day As IXMLDOMNode
        Set day = days(i)
        
        Dim attr As IXMLDOMNamedNodeMap
        Set attr = day.Attributes
        
        Dim year As String
        year = attr.getNamedItem("year").Text
        
        Dim month As String
        month = attr.getNamedItem("month").Text
        
        Dim value As String
        value = attr.getNamedItem("value").Text
    
    
        Dim price As String
        price = attr.getNamedItem("price").Text
    
        Dim volume As String
        volume = attr.getNamedItem("volume").Text
    
        Dim return_value As String
        return_value = attr.getNamedItem("return_value").Text
    
        Dim indication As String
        indication = attr.getNamedItem("indication").Text
    
        Dim work_end As String
        work_end = ""
        Dim node As IXMLDOMNode
        Set node = attr.getNamedItem("work_end")
        If Not node Is Nothing Then
            work_end = node.Text
        End If
        
        Set cur = cur.Offset(1)
        cur.Offset(, 0).value = year & "/" & month & "/" & value
        cur.Offset(, 1).value = price
        cur.Offset(, 2).value = volume
        cur.Offset(, 3).value = return_value
        cur.Offset(, 4).value = indication
        cur.Offset(, 5).value = work_end
    Next
    
    header.Font.Bold = True
    With header
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With header.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 16777164
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    header.AutoFilter
    header.EntireColumn.AutoFit
    
    Application.StatusBar = False
    Application.ScreenUpdating = True
    Application.Calculation = Calculation

    Dim s As Worksheet
    On Error Resume Next
    Set s = sht.Parent.Sheets(Left(indexName, 31))
    On Error GoTo 0
    If s Is Nothing Then
        sht.name = Left(indexName, 31)
    Else
        If vbOK = MsgBox("シートが重複しています。削除しますか?", vbOKCancel) Then
            s.Delete
            sht.name = Left(indexName, 31)
        End If
    End If
    
End Sub

Private Sub showSheetNames()
    Dim sht As Worksheet
    For Each sht In ThisWorkbook.Sheets
        Debug.Print sht.name
    Next
    
End Sub

2018-07-15

indexCheckの一覧を取得

var downloadAsTextFile = function(fileName, content) {
    var bom = new Uint8Array([0xEF, 0xBB, 0xBF]);
    var blob = new Blob([bom, content]);
    var url = window.URL || window.webkitURL;
    var blobURL = url.createObjectURL(blob);

    var a = document.createElement('a');
    a.download = fileName;
    a.href = blobURL;
    a.click();  
};
var downloadAsCSVFile = function(fileName, rows) {
  var content = "";
  for( var i in rows) {
    var ary = rows[i];
    for (var j = 0, m = ary.length; j < m; ++j) {
      content += '"' + ("" + ary[j]).replace('"', '""') + '"';
      if (j !== m) {
        content += ',';
      }
    }
    content += '\n';
  }
  downloadAsTextFile(fileName, content);
};


downloadAsCSVFile("index_list.csv", $("input[name='indexCheck']").toArray().map((e)=>[e.parentElement.innerText, e.value]));

http://www.morningstar.co.jp/FundData/common/chart/xml/all/<indexCheck>.xml

で,あたりが取り放題?