【金融数据分析】获取沪深300成分股权重数据方法优化

前面一篇文章已经介绍过获取沪深300成分股权重数据以及所属行业

【金融数据分析】计算沪深300指数行业权重分布并用饼图展示-CSDN博客

我们是通过下载到的excel文件来获取沪深300成分股数据的，不过沪深300指数的成分股是会变化的，如果每次更新都需要手动下载成分股数据则比较麻烦，我们可以在每次更新成分股数据的时候使用OkHttp库将excel表格下载下来，下载的代码如下

    private final String FILE_PATH_WEIGHT = "./000300closeweight.xls";
    // 获取一个OKHttp实例
    private OkHttpClient client = new OkHttpClient()
            .newBuilder()
            .connectTimeout(1000, TimeUnit.SECONDS)
            .build();

    // 下载两个excel文件
    private void downloadExcel(String url, String filename) {
        Request request = new Request.Builder()
                .url(url)
                .get()   //默认就是GET请求，可以不写
                .addHeader("User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.122 Safari/537.36")
                .build();
        try {
            Response response = client.newCall(request).execute();
            InputStream inputStream = response.body().byteStream();
            log.info("开始下载:" + filename);
            FileOutputStream fos = new FileOutputStream(filename);
            byte[] bytebuf = new byte[1024];
            int c;
            while(true) {
                c = inputStream.read(bytebuf);
                if(c == -1) break;
                fos.write(bytebuf, 0, c);
            }
            fos.close();
            response.body().close();
            log.info("下载完成:" + filename);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

我们在解析数据前首先下载一下数据表，然后再开始读取，这样的话每次获取到的数据就是最新的

     // 通过解析excel获取信息
    public void parseExcel() {
        downloadExcel(FILE_WEIGHT_URL, FILE_PATH_WEIGHT);
        ExcelReader excelReader = ExcelUtil.getReader(FILE_PATH_WEIGHT);
        excelReader.addHeaderAlias("成份券代码Constituent Code", "code");
        excelReader.addHeaderAlias("成份券名称Constituent Name", "name");
        excelReader.addHeaderAlias("权重(%)weight", "weight");
        csi300EntityList = excelReader.readAll(CSI300Entity.class);
        log.info("成功解析出" + csi300EntityList.size() + "条数据");
        log.info("开始解析所属行业");
        excelReader = ExcelUtil.getReader(FILE_PATH_INDUSTRY);
        excelReader.addHeaderAlias("证券代码", "code");
        excelReader.addHeaderAlias("证监会行业门类简称", "industry");
        List<CSI300Entity> csi300Entities = excelReader.readAll(CSI300Entity.class);
        for(int i=0; i<csi300Entities.size(); i++) {
            for(int j=0; j<csi300EntityList.size(); j++) {
                if (csi300EntityList.get(j).getCode().equals(csi300Entities.get(i).getCode())) {
                    csi300EntityList.get(j).setIndustry(csi300Entities.get(i).getIndustry());
                }
            }
        }
        // 首先清空数据表
        sqlIteCSI300Dao.clearAll();
        for(int i=0; i<csi300EntityList.size(); i++) {
            log.info(csi300EntityList.get(i).toString());
            sqlIteCSI300Dao.insertOneItem(csi300EntityList.get(i));
        }
    }

每次更新数据表前都需要有一个清空表的操作，当然也可以在表中记录成分股的更新时间，我这边图省事直接将之前的所有数据删了，使用JdbcTemplate清空表的代码如下

    @Override
    public void clearAll() {
        String sql = "DELETE FROM " + tableName;
        jdbcTemplate.batchUpdate(sql);
        log.info("成功清空数据表" + tableName);
    }

这样一来，我们就可以很方便地更新沪深300成分股数据了。

最后是完整的服务类的代码

@Slf4j
@Service
public class CSI300Service {
    private final String FILE_WEIGHT_URL = "https://csi-web-dev.oss-cn-shanghai-finance-1-pub.aliyuncs.com/static/html/csindex/public/uploads/file/autofile/closeweight/000300closeweight.xls";
    private final String FILE_PATH_WEIGHT = "./000300closeweight.xls";
    private final String FILE_PATH_INDUSTRY = "./行业分类.xlsx";
    private List<CSI300Entity> csi300EntityList;

    @Autowired
    private SQLIteCSI300Dao sqlIteCSI300Dao;

    // 获取一个OKHttp实例
    private OkHttpClient client = new OkHttpClient()
            .newBuilder()
            .connectTimeout(1000, TimeUnit.SECONDS)
            .build();

    // 下载两个excel文件
    private void downloadExcel(String url, String filename) {
        Request request = new Request.Builder()
                .url(url)
                .get()   //默认就是GET请求，可以不写
                .addHeader("User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.122 Safari/537.36")
                .build();
        try {
            Response response = client.newCall(request).execute();
            InputStream inputStream = response.body().byteStream();
            log.info("开始下载:" + filename);
            FileOutputStream fos = new FileOutputStream(filename);
            byte[] bytebuf = new byte[1024];
            int c;
            while(true) {
                c = inputStream.read(bytebuf);
                if(c == -1) break;
                fos.write(bytebuf, 0, c);
            }
            fos.close();
            response.body().close();
            log.info("下载完成:" + filename);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    // 通过解析excel获取信息
    public void parseExcel() {
        downloadExcel(FILE_WEIGHT_URL, FILE_PATH_WEIGHT);
        ExcelReader excelReader = ExcelUtil.getReader(FILE_PATH_WEIGHT);
        excelReader.addHeaderAlias("成份券代码Constituent Code", "code");
        excelReader.addHeaderAlias("成份券名称Constituent Name", "name");
        excelReader.addHeaderAlias("权重(%)weight", "weight");
        csi300EntityList = excelReader.readAll(CSI300Entity.class);
        log.info("成功解析出" + csi300EntityList.size() + "条数据");
        log.info("开始解析所属行业");
        excelReader = ExcelUtil.getReader(FILE_PATH_INDUSTRY);
        excelReader.addHeaderAlias("证券代码", "code");
        excelReader.addHeaderAlias("证监会行业门类简称", "industry");
        List<CSI300Entity> csi300Entities = excelReader.readAll(CSI300Entity.class);
        for(int i=0; i<csi300Entities.size(); i++) {
            for(int j=0; j<csi300EntityList.size(); j++) {
                if (csi300EntityList.get(j).getCode().equals(csi300Entities.get(i).getCode())) {
                    csi300EntityList.get(j).setIndustry(csi300Entities.get(i).getIndustry());
                }
            }
        }
        // 首先清空数据表
        sqlIteCSI300Dao.clearAll();
        for(int i=0; i<csi300EntityList.size(); i++) {
            log.info(csi300EntityList.get(i).toString());
            sqlIteCSI300Dao.insertOneItem(csi300EntityList.get(i));
        }
    }

}

最后我们可以将接口添加到前端页面，实际上就是添加一个按钮，点击后调用接口开始更新数据，效果如下所示

我们添加了一个<el-card>用来存放更新状态以及更新的按钮，点击按钮后会调用后端接口进行数据更新，开始数据更新和更新成功后都会有一个ElMessage进行消息提示。

        <el-card class="box-card">
          <template #header>
            <div class="card-header">
              <span>更新沪深300成分股数据</span>
            </div>
          </template>
          <el-form>
            <el-form-item>
              <el-text>信息更新状态：{{ update_status }}</el-text>
            </el-form-item>
            <el-form-item>
              <el-text>操作：</el-text>
              <el-button type="primary" size="medium" @click="updateCSI300"
                >重新更新信息</el-button
              >
            </el-form-item>
          </el-form>
        </el-card>

?调用接口的代码如下

    updateCSI300() {
      var url = "http://localhost:9001/parse";
      this.update_status = "更新中";
      ElMessage("开始更新数据");
      axios
        .get(url)
        .then((response) => {
          console.log(response);
          // 调用接口成功则返回成功提示框
          ElMessage({
            message: "更新数据成功",
            type: "success",
          });
          this.update_status = "更新成功";
          // 重新初始化数据
          this.loading = true;
          this.init();
        })
        .catch((error) => {
          console.log(error);
          this.update_status = "更新失败";
          // 调用接口失败则返回失败提示框
          ElMessage.error("更新数据失败");
        });
    },

最后是完整的前端页面代码

<template>
  <div>
    <el-row class="container">
      <div class="left-grid">
        <el-card class="box-card">
          <template #header>
            <div class="card-header">
              <span>更新沪深300成分股数据</span>
            </div>
          </template>
          <el-form>
            <el-form-item>
              <el-text>信息更新状态：{{ update_status }}</el-text>
            </el-form-item>
            <el-form-item>
              <el-text>操作：</el-text>
              <el-button type="primary" size="medium" @click="updateCSI300"
                >重新更新信息</el-button
              >
            </el-form-item>
          </el-form>
        </el-card>
        <el-card>
          <el-table
            v-loading="loading"
            :data="table_data"
            :show-header="true"
            :max-height="430"
            stripe
          >
            <el-table-column
              type="index"
              label="序号"
              width="65%"
            ></el-table-column>
            <el-table-column prop="code" label="股票代码"></el-table-column>
            <el-table-column prop="name" label="公司简称"></el-table-column>
            <el-table-column prop="industry" label="所属行业"></el-table-column>
            <el-table-column prop="weight" label="权重占比"></el-table-column>
          </el-table>
        </el-card>
      </div>

      <div class="right-grid" ref="myChart"></div>
    </el-row>
  </div>
</template>

<script>
import axios from "axios";
import { ElMessage } from "element-plus";
import { getCurrentInstance } from "vue";
export default {
  data() {
    return {
      update_status: "未开始",
      loading: true,
      table_data: [],
      pie_data: [],
      echarts: getCurrentInstance().appContext.config.globalProperties.$echarts,
    };
  },
  mounted() {
    this.init();
  },
  methods: {
    init() {
      var url = "http://localhost:9001/queryAll";
      axios
        .get(url)
        .then((response) => {
          this.table_data = response.data;
          console.log(response);
          this.loading = false;
        })
        .catch((error) => {
          console.log(error);
          this.loading = false;
        });
      this.create_pie();
    },
    // 绘制饼状图
    create_pie() {
      var url = "http://localhost:9001/queryDist";
      axios
        .get(url)
        .then((response) => {
          console.log(response);
          this.pie_data = [];
          for (var i = 0; i < response.data.length; i++) {
            this.pie_data.push({
              value: response.data[i].weight,
              name: response.data[i].industry,
            });
          }
          console.log(this.pie_data);
          var myChart = this.echarts.init(this.$refs["myChart"]);
          var option = {
            title: {
              text: "沪深300行业权重分布", //标题
            },
            tooltip: {},
            legend: {
              y: 50,
              textStyle: {
                fontSize: 14,
              },
            },
            label: {
              show: true,
            },
            series: [
              {
                name: "分布", //数据的名字
                type: "pie", //表示柱状图
                radius: "70%", //圆的半径
                center: ["50%", "60%"],
                label: {
                  formatter: function (params) {
                    console.log(params.name + " " + params.value + "%");
                    return params.name + " " + params.value + "%";
                  },
                  textStyle: {
                    fontSize: 14,
                    fontWeight: "bolder",
                  },
                  color: "inherit",
                },
                data: this.pie_data,
                selectedMode: "single", //选中效果，使选中区域偏离圆心一小段距离，single或者multiple
                selectedOffset: 10, //偏离圆心的一小段距离
              },
            ],
          };
          // 使用刚指定的配置项和数据显示图表。
          myChart.setOption(option);
        })
        .catch((error) => {
          console.log(error);
        });
    },
    // 更新沪深300成分股数据
    updateCSI300() {
      var url = "http://localhost:9001/parse";
      this.update_status = "更新中";
      ElMessage("开始更新数据");
      axios
        .get(url)
        .then((response) => {
          console.log(response);
          // 调用接口成功则返回成功提示框
          ElMessage({
            message: "更新数据成功",
            type: "success",
          });
          this.update_status = "更新成功";
          // 重新初始化数据
          this.loading = true;
          this.init();
        })
        .catch((error) => {
          console.log(error);
          this.update_status = "更新失败";
          // 调用接口失败则返回失败提示框
          ElMessage.error("更新数据失败");
        });
    },
  },
};
</script>

<style scoped>
.card-header {
  display: flex;
  justify-content: space-between;
  align-items: center;
}
.container {
  display: grid;
  grid-template-columns: 35% 65%;
  width: 100%;
  height: 80vh;
}
.left-grid {
  background-color: #f0f0f0;
  border-radius: 2%;
  padding: 10px;
  height: 95%;
}
.right-grid {
  background-color: #f9ecc3;
  border-radius: 2%;
  padding: 10px;
  height: 95%;
}
</style>