Need help with a simple excel calculation for massive data...

jamaljan

New Member
Joined
Apr 8, 2010
Messages
35
hello everyone

I'm working on the last 15 years data which am extracting from a website. I started working manually and realized that it will take me forever to compile the data.

Basically, am taking everyday's data for a month, adding all the values and averaging them - pasting the data on a new sheet and then calculating its monthly low, monthly high, yearly-average low and yearly-average high.

I'm pasting below the sheet that am trying to compile from the data that is available. Please let me know if there is an easier way to do it.


1596140721876.png




Following is basically how the data looks like which am extracting from a website.
1596140822649.png
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,307
Office Version
  1. 365
Platform
  1. Windows
Power Query was built for this purpose. Do you know PQ? It can get the data from the website and transform it.

And the extracted data you show there can be quickly summarised with a Pivot Table.
 

jamaljan

New Member
Joined
Apr 8, 2010
Messages
35
Power Query was built for this purpose. Do you know PQ? It can get the data from the website and transform it.

And the extracted data you show there can be quickly summarised with a Pivot Table.

Thanks, am not aware of PQ. The data needs to be extracted from website, one symbol at a time(in csv format) - there doesn't seem to be any other way around it.

I've worked on Pivot tables but very simple ones. This data that I need seems a little complex and I need to do it for multiple symbols. Is there anything else that I can do about it plz?
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,307
Office Version
  1. 365
Platform
  1. Windows
What's the web address for one of the stock symbols?
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,307
Office Version
  1. 365
Platform
  1. Windows
So what will happen is that you will download from that website each and every stock symbol of interest, which could be dozens. The good news is that they will al be the same shape of database, which is to say they will all have the same field names with stock symbol being the database key.

My advice is to put all the csv files in one folder smartly named (like Quotations). Then, using Power Query you can conduct operations on all of them at once. After that, you can use a Pivot Table to create your summaries. Every once in a while you can go about downloading all those csv files to your folder, and then in Excel press Refresh All and the whole project will be done so fast your head will spin.

Here are some links to some videos on youtube from ExcelIsFun. Watch these and others about PQ in that channel and you will see how efficient this will all be.

And a playlist: Power Query (Get & Transform) Video Playlist of Videos
 

jamaljan

New Member
Joined
Apr 8, 2010
Messages
35
Many thanks, will look into it and get back here if I need any further guidance :)
 

Watch MrExcel Video

Forum statistics

Threads
1,112,784
Messages
5,542,500
Members
410,559
Latest member
jordansmith6532
Top