Retrieving data from website to Excel File

• Durum: Closed
• Ödül: $20
• Alınan Girdiler: 3
• Kazanan: teachartdevteam

Winner

Yarışma Özeti

Hi all!

I am looking for someone to automate a process which is currently done manually. I am not that interested on the output, I would like to get a tool, a macro or a code because I iwll have to run this several times on the future. Fell free to use any coding languague you would like.

These are the step:

1. Go to the following link and select the month and the year of the report: http://www.spensiones.cl/safpstats/stats/getPerInfo.php?infoid=cinvAFP

2. For example if you choose Decemeber 2012 you will get to this link:
http://www.spensiones.cl/safpstats/stats/inf_estadistica/cinvAFP/2012/12/cinv201212.html

In this page you will find an index with many report names and their respective code. I am only interested in Report number 21.Cartera de los Fondos de Pensiones por Tipo de Fondo y Total de Fondos: Inversión en el extranjero por emisor e instrumento y AFP. You will have to click on the name of each sub report (Fondo A, Fondo B, Fondo C, Fondo D, Fondo E... I am not interested on the subreport Total Fondos). For this example, lets start with Fondo A and you will be redirected to the following page: https://www.spensiones.cl/admusuarios/carteras/genera_desagregada_xsl.php?fecpro=201212&listado=21&tipofondo=A

3. Then I am pasting all of this information in an excel file and rearreging the data so that every data point is a row. Please see attached file to review how the output should look like. In the attached file, the grey tabs are an example of each subreport (Fondo A, Fondo B...), the orange tab is the consolidation of each subreport (of each grey tab) by date (in this case July 2017), and the blue tabs are outputs for other dates.

4. The tool/macro/code should ask to input the start date and the end date and should egenerate an excel file as the one attached.

If you have any question please let me know and thank you for your help!

Cristobal

Aranan Beceriler

• Excel
• Excel Macros
• Excel VBA
• Web Scraping