Hello Internet!
I have a spreadsheet where I make some very simple calculations. We could call it a report, where I divide some single cells with others and get ratios and growth rates etc. (very simple stuff).
Anyway..
Every week, I input new values into some of the cells in this report and I get a new output of ratios and figures for that week. It's only a matter of three or four cells where I need to change to the new values. I find these figures in a PDF that a bank sends me weekly. So I usually scroll through the PDF to find the new figure, and I copy it, and I paste it into a cell in my spreadsheet and my calculation is updated.
What I'd like to do is to automate this entire process. I want to set up a macro that can go fetch that figure in the PDF and update my input cell with the new value so that I get an updated output value with the push of a button. That would be so neat!
However, I have no idea how to set this up. I don't really need to import an entire data set, only those three or four values in the massive table that is inside the PDF.
I realize that Excel might not be able to scrape a PDF, so I've asked the bank to send me CSV:s of the same table that is usually in the PDF. Perhaps a CSV makes this whole thing easier and I could avoid using a third party scraping software?
... So I guess what I instead should try to do is to make a macro that with the push of a button, goes into a certain folder, finds the latest CSV that I've downloaded, imports the CSV to a new sheet in Excel, then searches through the new table, and finds the cell where the figure I'm looking for is, then takes that value and puts it in the input cell in my little report sheet, and thus updates the output to the new weeks value.
I specifically need the macro to search through the CSV-made table to find the figure, because the table will vary in size, and the value I want to extract doesn't always show up in the same spot (nor at the same pdf-page for that matter).
I think the hard part is scraping the CSV (or PDF) for a single value that doesn't always show up in the same place. And also having the macro finding each weeks new file in a folder and update the data table. I think the scraping could be achieved by having the macro find the value by using the row and column name, which should be the same each time, though it ends up on a different page depending on the length of the table. And I think the automatic importing of the newest CSV data set could be achieved by simply naming the file the same thing in the folder, replacing the old one every week upon downloading it.
I've been googling for a couple of days to learn how to what I've attempted to describe above, but without luck. So now I figured I'd try my luck with the community! Any help would be massively appreciated, I'd really like to learn how to increase efficiency through macro-making, and this is a great opportunity for learning some basics. Thanks to anyone who took the time to read this. Once I've learned some more I'll be sure to also contribute to the community by helping others.
Cheers!
I have a spreadsheet where I make some very simple calculations. We could call it a report, where I divide some single cells with others and get ratios and growth rates etc. (very simple stuff).
Anyway..
Every week, I input new values into some of the cells in this report and I get a new output of ratios and figures for that week. It's only a matter of three or four cells where I need to change to the new values. I find these figures in a PDF that a bank sends me weekly. So I usually scroll through the PDF to find the new figure, and I copy it, and I paste it into a cell in my spreadsheet and my calculation is updated.
What I'd like to do is to automate this entire process. I want to set up a macro that can go fetch that figure in the PDF and update my input cell with the new value so that I get an updated output value with the push of a button. That would be so neat!
However, I have no idea how to set this up. I don't really need to import an entire data set, only those three or four values in the massive table that is inside the PDF.
I realize that Excel might not be able to scrape a PDF, so I've asked the bank to send me CSV:s of the same table that is usually in the PDF. Perhaps a CSV makes this whole thing easier and I could avoid using a third party scraping software?
... So I guess what I instead should try to do is to make a macro that with the push of a button, goes into a certain folder, finds the latest CSV that I've downloaded, imports the CSV to a new sheet in Excel, then searches through the new table, and finds the cell where the figure I'm looking for is, then takes that value and puts it in the input cell in my little report sheet, and thus updates the output to the new weeks value.
I specifically need the macro to search through the CSV-made table to find the figure, because the table will vary in size, and the value I want to extract doesn't always show up in the same spot (nor at the same pdf-page for that matter).
I think the hard part is scraping the CSV (or PDF) for a single value that doesn't always show up in the same place. And also having the macro finding each weeks new file in a folder and update the data table. I think the scraping could be achieved by having the macro find the value by using the row and column name, which should be the same each time, though it ends up on a different page depending on the length of the table. And I think the automatic importing of the newest CSV data set could be achieved by simply naming the file the same thing in the folder, replacing the old one every week upon downloading it.
I've been googling for a couple of days to learn how to what I've attempted to describe above, but without luck. So now I figured I'd try my luck with the community! Any help would be massively appreciated, I'd really like to learn how to increase efficiency through macro-making, and this is a great opportunity for learning some basics. Thanks to anyone who took the time to read this. Once I've learned some more I'll be sure to also contribute to the community by helping others.
Cheers!
