月營收增加股票篩選 | 用程式打造選股策略(8)

前言

最近台股看起來真的很強勢，且台灣的防疫到目前為止確實都做得不錯，
那麼正巧近期剛好月報即將公布，那麼就來做個簡單的月營收增加排行吧！

利用 用 C# .NET Core 爬取每月財報 這篇爬下來的資料
這裡希望挑選出營收月增的條件如下:

1. 當月營收 > 上個月營收
2. 當月營收 > 去年同月份營收

程式部分

後端: .NET Core Web API
前端: Vue.js

後端

建立回傳的Model:

public class MonthRevenueIncrease
{
    public string stock_id { get; set; }
    public string stock_name { get; set; }
    public decimal last_month_increase { get; set; } // 和上個月比增加的倍數
    public decimal last_year_increase { get; set; }  // 和去年同月比增加的倍數
    public int this_revenue { get; set; }
    public int last_month_revenue { get; set; }
    public int last_year_revenue { get; set; }
}

SQL & Repository

這裡設計成可以由使用者選擇年月，方便比較各月月增的狀況
挑選的邏輯這裡直接用SQL解決了，程式如下:

public class RevenueRepository
{
    private readonly SqlConnection _conn;
    private readonly ILogger<RevenueRepository> _logger;
    public RevenueRepository(ILogger<RevenueRepository> logger, SqlConnection conn)
    {
        _logger = logger;
        _conn = conn;
    }

    public IEnumerable<MonthRevenueIncrease> GetMonthRevenueIncrease(int year, int month)
    {
        return _conn.Query<MonthRevenueIncrease>(
@"select stockinfo.stock_id, 
stockinfo.stock_name, 
(CONVERT (decimal, mr1.revenue)/CONVERT (decimal, mr2.revenue)) last_month_increase, 
(CONVERT (decimal, mr1.revenue)/CONVERT (decimal, mr3.revenue)) last_year_increase,
mr1.revenue this_revenue,
mr2.revenue last_month_revenue,
mr3.revenue last_year_revenue
from MonthReport mr1, MonthReport mr2, MonthReport mr3, 
(select distinct stock_id, stock_name from TaiwanStockInfo) stockinfo
where 
mr1.year = @year and mr1.month = @month and
mr2.year = @last_month_year and mr2.month = @last_month and
mr3.year = @last_year and mr3.month = @month and
mr1.stock_id = mr2.stock_id and
mr2.stock_id = mr3.stock_id and
mr1.stock_id = stockinfo.stock_id and
CONVERT (int, mr1.revenue) > CONVERT (int, mr2.revenue) and 
CONVERT (int, mr1.revenue) > CONVERT (int, mr3.revenue) and 
mr1.revenue > 0 and mr2.revenue > 0 and mr3.revenue > 0
order by last_year_increase desc, last_month_increase desc",
            new {
                year,
                month,
                last_year = year - 1,
                last_month = month == 1 ? 12 : month - 1,
                last_month_year = month == 1 ? year - 1 : year
            }
        );
    }
}

Web Api

很簡單，就只是丟Json到前端去處理

[ApiController]
[Route("api/[controller]/[action]")]
public class RevenueController
{
    private readonly ILogger<RevenueController> _logger;
    private RevenueRepository _revenueRepository;

    public RevenueController(ILogger<RevenueController> logger, RevenueRepository revenueRepository)
    {
        _logger = logger;
        _revenueRepository = revenueRepository;
    }

    [HttpGet]
    public IActionResult GetMonthRevenueIncrease(int year, int month)
    {
        return new JsonResult(_revenueRepository.GetMonthRevenueIncrease(year, month));
    }
}

前端

這裡直接用一個Table呈現，
太簡單了，好像沒什麼好說的，直接看程式吧！

<template>
  <div class="container">
    <h2>月營收增加排行</h2>
    <div class="select-items">
      <select v-model="query.year" @change="getMonthRevenueIncrease()">
        <option v-for="year in years" :value="year" :key="year">{{year}}</option>
      </select>
      <span>年</span>
      <select v-model="query.month" @change="getMonthRevenueIncrease()">
        <option v-for="month in months" :value="month" :key="month">{{month}}</option>
      </select>
      <span>月</span>
    </div>
    <div v-if="monthRevenueIncreaseList === null">Loading...</div>
    <table v-else class="month-revenue-table">
      <caption>單位: 新台幣仟元</caption>
      <thead>
        <tr>
          <th>股票代號</th>
          <th>股票名稱</th>
          <th>單月年增</th>
          <th>單月月增</th>
          <th>月營收</th>
          <th>上月營收</th>
          <th>去年同月營收</th>
        </tr>
      </thead>
      <tbody>
        <tr v-if="monthRevenueIncreaseList.length === 0">
          <td colspan="7">查無資料</td>
        </tr>
        <tr v-else v-for="monthRevenueIncrease in monthRevenueIncreaseList" :key="monthRevenueIncrease.stock_id">
          <td>{{monthRevenueIncrease.stock_id}}</td>
          <td>{{monthRevenueIncrease.stock_name}}</td>
          <td>{{((monthRevenueIncrease.last_month_increase - 1) * 100).toFixed(2)}}%</td>
          <td>{{((monthRevenueIncrease.last_year_increase - 1) * 100).toFixed(2)}}%</td>
          <td>{{monthRevenueIncrease.this_revenue}}</td>
          <td>{{monthRevenueIncrease.last_month_revenue}}</td>
          <td>{{monthRevenueIncrease.last_year_revenue}}</td>
        </tr>
      </tbody>
    </table>
  </div>
</template>

<script>
export default {
  name: 'MonthRevenueIncrease',
  data () {
    return {
      monthRevenueIncreaseList: null,
      years: Array(10).fill(new Date().getFullYear() - 1911).map((item,index) => item - index),
      months: Array(12).fill().map((item, index) => index + 1),
      query: {
        year: new Date().getFullYear() - 1911,
        month: new Date().getMonth() - 1
      }
    }
  },
  async created () {
    await this.getMonthRevenueIncrease();
  },
  methods: {
    async getMonthRevenueIncrease() {
      let response = await this.axios.get(`/api/Revenue/GetMonthRevenueIncrease?year=${this.query.year}&month=${this.query.month}`)
      this.monthRevenueIncreaseList = response.data
    }
  }
}
</script>

<style lang="stylus" scoped>
  h2 
    margin 20px 0px
    
  .select-items
    margin 10px
    
    select 
      margin 0px 5px

  .month-revenue-table
    margin 0 auto

    caption
      text-align: right;
      padding: 5px 2px;
      font-size:14px
    tr
      th, td
        border 1px solid #ccc
        padding 7px 9px
</style>

結果

今天的日期是 2020/05/10 ，因此這邊就以 2020/04 資料為範例
由於篩選出的檔數太多了，總共篩出206檔股票..
然後我也懶得打包這份Component，因此這裡就直接看截圖出來的部分資料吧…

心得

最近實在想不到有什麼好的策略可以做，這篇算是偷懶文了吧…
如果有人有一些不錯的Idea歡迎留言告訴我！

↓↓↓ 如果喜歡我的文章，可以幫我按個Like！ ↓↓↓

>> 或者，請我喝杯咖啡，這樣我會更有動力唷！ <<<

街口支付

街口帳號: 901061546