【金融数据分析】获取沪深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));
        }
    }

}