Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Transfering updated data from web to another sheet in same workbook


I have a number of different shares and have developed a sheet to show the profit / loss. I have an autoupdate from the web using api and would like to record the changes so that a profit and loss graph can be made. I am struggling to even find the right terms to use to search for a solution on the web. I would like each change to be recorded and put in a new cell so a graph can be created. If a time stamp could be put on then even better. Any help appreciated



Selected Answer

You basically just need 3 things:

  • Get the next empty row for the data to store.
  • Get the time.
  • Input the data.

Here is a simple macro that will do that.

Sub dataInput()

'Get the next empty row
NextRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).Row

'Get the time
currentTime = Time()

'    Input the data

'Imported value.
Sheets("Sheet1").Cells(NextRow, 1).Value = "Your Value"
Sheets("Sheet1").Cells(NextRow, 2).Value = currentTime

End Sub

Sheet1 is the sheet where you want to store the data.

The macro assumes that the data will start in Column A.

When inputting the data, look to this part (NextRow, 1). 1 is the column where that data will be placed. 1 means Column A; 2 means Column B, etc.



Wow, quick response. I now need to learn macros and how to implement them. Many thanks
BillyBob (rep: 2) Dec 19, '17 at 1:56 am
Have a look at our free course on macros: Introduction to Programming Macros in Excel
don (rep: 1979) Dec 22, '17 at 1:29 pm
Add to Discussion

Start this project with the workbook design. Leave the code till last.

  1. You will need a master list of all the stocks you own. In this sheet you need a column for the quantity you own, price per share you paid and another column for the current value, also per share (or per lot). A column to compare those values would give you an immediate P/L value.
  2. Since you may buy and sell your shares a major step in your project would be to have a separate sheet for each share in which to record transactions with their dates, and from which you carry an average cost and the current quantity to the master sheet. Perhaps this is something you might add on in the future.
    (In my own workbook of this type I tied the individual sheets to an accounts sheet and then generated the master from the individuals.)
  3. For your scrapes you need a history sheet where the shares are in columns and you add a row for each scrap. I recommend to add new rows in this sheet at the top (2nd or 3rd row) instead of the bottom and keep the master in sync with the lastest row as part of the scraping process. If you wish to have daily changes (up, down or unch) the best place to calculate them is in the history sheet where you would need an extra column for that purpose for each share. You can then copy the mark to the master (where you don't have the previous price so easily available).

The design of the sheets and the design of the work flow, and how the sheets interact with each other) determines the code you need. I recommend that you work 100% manually with your design for a couple of weeks. You will find missing columns and decide about formats. Then, when everything actually works fine, you can start automating things, one process, one link, at a time. My own project was started more than 10 years ago and I still add features to it now and then. This is meant as a recommendation to invest time in finding meaningful variable names and add plenty of comments.



Thank you. I am on the right track with each stock in their own sheet. Just need to implement the macro above. 
BillyBob (rep: 2) Dec 19, '17 at 1:57 am
Managed to sort out the code for the copy and paste functions. Hard part was getting it to update on a specific time scale. Found this line of code that works perfectly without having to create loop or dim integers.
Application.OnTime Now + TimeValue("00:15:00"), "MyMacro" 

Thank you for your responses. Have realised just how powerful Excel is. A great case of I didn't know what I didn;t know. 
BillyBob (rep: 2) Dec 20, '17 at 7:01 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login