Shulou(Shulou.com)06/02 Report--

How python crawls the latest data of fund stocks and draws a tree chart with excel. In view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

Hello, everyone. Recently, the white horse stocks of Big A simply fell and their mother did not recognize it. As a young chicken breeder who held a large white horse stock fund, he washed his face with tears every day.

Mom doesn't admit it.

However, judging from the market chart of a recent trading day in the financial sector, in fact, many small and medium-sized stocks are still red, and the green ones are white horses.

So, today we try to crawl stock data from the most recent trading day with python, and try to simply draw the tree above with excel.

Crawl the stock data of various sectors of NetEase Finance and Economics.

Excel tree view

A simple tree view

Tree with growth rate

First, crawl the stock data of various sectors of NetEase Finance and Economics.

Target URL:

Http://quotes.money.163.com/old/#query=hy010000&DataType=HS_RANK&sort=PERCENT&order=desc&count=24&page=0

NetEase Finance and Economics-Market Center

As the crawler part is relatively simple, I will not elaborate too much here, just introduce the train of thought and attach the complete code for your reference.

Crawler ideas:

Request the data of the target website to parse the data of the major industries (new): the name of the industry sector and the corresponding id (e.g. Finance, hy010000)

Construct a new web page of industry stock data according to the corresponding id of industry plate

Since the page turning URL remains the same, insert the parameter, get all the pages, and then turn the page to crawl all the data.

Crawler code:

#-*-coding: utf-8-*-"Created Feb 28 10:30:56 2021@author: you can call me brother Cai" import requestsimport reimport pandas as pd# gets all the plates and plates idurl = 'http://quotes.money.163.com/old/#query=hy001000&DataType=HS_RANK&sort=PERCENT&order=desc&count=24&page=0'r = requests.get (url) html = r.text# replace non-character blank It is convenient for the following regular html = re.sub ('\ s regular acquisition plate and the region where id is located labelHtml = re.findall (r 'major industries\ (new\) (. *?)) Securities Regulatory Commission Industry\ (New\)', html) [0] # regular Plate and id The result is a list of tuples label = re.findall (r'"qid=" (hy.*?) "qquery=.*?" title= "(. *?)" >', labelHtml) # transformed into dataframe type dfLabel = pd.DataFrame (label,columns= ['id',' plate']) # get page data (json format) according to id and page flip (hy_id) Page): query = 'PLATE_IDS:' + str (hy_id) params= {' host': 'http://quotes.money.163.com/hs/service/diyrank.php',' page': page, 'query': query,' fields': 'NO,SYMBOL,NAME,PRICE,PERCENT,UPDOWN,FIVE_MINUTE,OPEN,YESTCLOSE,HIGH,LOW,VOLUME,TURNOVER,HS,LB,WB,ZF,PE,MCAP,TCAP,MFSUM MFRATIO.MFRATIO2,MFRATIO.MFRATIO10,SNAME,CODE,ANNOUNMT,UVSNEWS', # you don't need so many fields' sort': 'PERCENT',' order': 'desc',' count': '24th,' type': 'query',} url =' http://quotes.money.163.com/hs/service/diyrank.php?' R = requests.get (url,params=params) j = r.json () return j # empty list is used to access each page of data dfs = [] # traverse all plates for hy_id, plate in dfLabel.values: # get pages j = get_json (hy_id, 0) pages = j ['pagecount'] for page in range (pages): J = get_json (hy_id Page) data = j ['list'] df = pd.DataFrame (data) df [' plate'] = plate dfs.append (df) print (f 'has crawled {len (dfs)} plate data') result = pd.concat (dfs) II. Excel tree view

Excel tree view is a new chart type added in the version after office2016 level. If you want to draw it, you need to base it on this version and later versions.

Simple tree view drawing process: frame select data-> insert-> chart-> select tree view.

Tree drawing process

The following figure is an example. In the tree view, each color block represents a province, and the size of the color block area is determined by its GDP value.

2020 GDP across the country

Tree diagram with growth rate

We find that in the basic tree view, the color of the color block has no special meaning except to distinguish the color block. In the case of GDP, in addition to the value, we usually also look at its growth rate, so can there be a correlation between color block color and growth rate?

Let's try to explore, if successful, then the cloud map of the financial world seems to be able to use the excel tree to draw it!

1. Train of thought:

We want the color to represent the growth rate, for example, red is rising, green is falling, and the darker the color is, the greater the absolute value is.

And then fill each color block with the corresponding color.

Since the tree view can support multiple levels at most, the color blocks can only be filled manually, so what should we do? Since you can do it manually, you can actually automate the process with VBA.

2. Growth rate color matching

Based on the above ideas, we need to match the growth rate, and the simplest thing is to use the color levels in the conditional format.

Select the growth rate data-> start-> conditional format-> Color level (select the one that makes the color redder with the higher the value, because there is a negative growth rate here, the one with red and green is selected:

In order to better show the difference between positive and negative growth rates, we will manage the rules after setting the color level:

We set the median to the number 0, so the negative growth rate is green and the positive growth rate is red.

We set the maximum value to a percentile value of 80, that is, the top 80% of the growth rate is the reddest.

Final color matching effect:

Color matching effect

4. VBA fill block color

Let's first look at the effect:

Hubei is the most affected by the epidemic, which is closed for nearly half a year, with a negative growth rate for the whole year.

Provincial GDP and growth rate

Since the cell color is not fixed in the conditional format and cannot be obtained through vba, we need to assign the color to a new column, using the following operations:

Select the growth rate data copy, and then click on the lower right corner of the clipboard will appear clipboard, and then the left mouse button to select the place to paste such as E2, click on the clipboard to paste the data. At this point, the color of the pasted range of cells is fixed, and you can choose to delete the data and leave only the color part.

Operation flow of cell color copy

VBA ideas:

Activate the chart that requires action (Activate)

Traverse all series and data points (ActiveChart.FullSeriesCollection (1) .Points.Count)

Starting from the first data point, get the corresponding growth rate cell color (ActiveSheet.Range ("E" & I + 1) .Interior.Color)

Assign a cell to the data point (Selection.Format.Fill.ForeColor.RGB)

VBA Code:

Sub My_Color () ActiveSheet.ChartObjects ("Chart 1"). Activate 'traverses all data points For I = 1 To ActiveChart.FullSeriesCollection (1). Points.Count' selects data points ActiveChart.FullSeriesCollection (1) .points (I). Select 'gets the cell color MyColor = ActiveSheet.Range ("E" & I + 1). Interior.Color' assigns the cell color to the corresponding data point fill color Selection.Format.Fill.ForeColor.RGB = MyColor Next

The procedure for executing the script is as follows:

This is the answer to the question about how python crawls the latest data of fund stocks and draws a tree chart with excel. I hope the above content can be of some help to everyone. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.