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

[jamaljan]

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.

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

 

[DRSteele]

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]

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]

What's the web address for one of the stock symbols?

 

[jamaljan]

What's the web address for one of the stock symbols?

I'm using this website... ksestocks.com | Daily Closing Summaries & Quotation History Files | Pakistan Stock Exchange (PSX)

 

[DRSteele]

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

 

[DRSteele]

This one is better.

 

[jamaljan]

Many thanks, will look into it and get back here if I need any further guidance