Find Jobs
Hire Freelancers

Build VBA code to download stock prices into Excel

$30-90 USD

Concluído
Publicado há mais de 11 anos

$30-90 USD

Pago na entrega
**Build VBA code in an Excel spreadsheet to do the following.** *Note that it is only preferred that it is VBA code.* Input a stock code from a cell in Excel, into a Yahoo Finance website field. To then ask this website to retrieve historical price data on this code. Then to place this data into an Excel Spreadsheet. Perform some basic verification checks on the downloaded data. If it passes these checks to then place into a final 'Data' sheet where this data will be stored. To repeat this 200-300 times on all codes; building up the data in the 'Data' sheet. ## Deliverables Detailed Process to be followed. 1. Open up Excel workbook called "Historical Stock Data 1" 2. In Sheet 1 called "Stock Codes" there is a column called CODE starting in D11. In the workbook attached there are 236 codes. 3. Open up Yahoo Finance website: <[login to view URL]> 4. There is a field called: Enter Symbol 5. In this field enter the 1st Stock Code. Namely; AAPL 6. Yahoo will return several possible stock to pick. It is _vital_ that you pick the stock that has the same name as the code. For AAPL this is: Apple Inc. **Note:** Sometimes there are more than 1 stock per code because the codes are not unique across all global markets, and Yahoo scans global markets not just US markets for the symbol (CODE). The Name of the stock is in the column to the left of the codes in the example spreadsheet provided. 6. Select the correct Stock with associated code and click: GET QUOTE 7. You will be taken to a 'Summary' page for the specified stock. 8. In the left hand column you will see a field called "Historical Prices". Click on this field. 9. Yahoo now presents a table with Historical Prices in it. 10. At the top of this table is a set of input fields called "Set Date Range" 11. Set start date to 01 01 2000 in the 3 fields for day, Month and Year. **Note:** I do not require data beyond this date. The end date should default to the latest date where data was available, and the data time period should default to "Daily" on the right hand side. 12. Click on "Get Prices" Button in the Set Data Range box. 13. Once this task has been executed, scroll to the bottom of the page. There you will find hyper- text called "Download to spreadsheet". 14. Click on "Download to Spreadsheet". 15. You are then presented with an option in Microsoft to open the file with Excel or to save the file. Currently I then open the file with excel. 16. You now have an Excel workbook with the data in it. 17. Column A to G now have the data with the headings: DATE, OPEN, HIGH, LOW, CLOSE, VOLUME, ADJ VOLUME. 18. Before we can use this data we need to make sure it passes some data checks. Check 1. - Does the data go back more than 3 years from the start date at the top of the sheet? If it does not then this data cannot be used. Check 2. - Does the Adjusted Close (last column) at any point in the data set dip below 1? If it does, the data cannot be used. Check 3. - Are there any gaps in the data? In other words, are there any empty cells in the data set, start to finish? - If there are the data cannot be used. Check 4. - Is the date at the top of the sheet (most recent date) within the last week of the execution of this process? If not, then the data cannot be used. **If a CODE fails any of these checks then the code needs to be noted down in an Excel sheet with the Check(s) that it failed.** 19. If the data passes all these checks then it needs to be copied and pasted (values only) into the Excel sheet called "Data1". The headings are as shown in the example spreadsheet. These are the same as the downloaded data but with the addition of CODE in the final column and RECORD in the first column. Note that record numbers include separator blank rows. 20. The formats should remain as shown in the example sheet. 21. The column headings should only appear once in row 1. After this all additional data is added to the bottom of the data with a 1 row gap. This gap is vital as my VBA code uses this gap to differentiate between data sets. 22. Repeat for all codes. Additional NOTES. The exact process used is the process I use to manually extract and verify and collate the required data. It does not have to be done this way so long as the end result is the same...
ID do Projeto: 2785758

Sobre o projeto

8 propostas
Projeto remoto
Ativo há 11 anos

Quer ganhar algum dinheiro?

Benefícios de ofertar no Freelancer

Defina seu orçamento e seu prazo
Seja pago pelo seu trabalho
Descreva sua proposta
É grátis para se inscrever e fazer ofertas em trabalhos
Concedido a:
Avatar do Usuário
See private message.
$50,15 USD em 3 dias
5,0 (17 avaliações)
3,4
3,4
8 freelancers estão ofertando em média $67 USD for esse trabalho
Avatar do Usuário
See private message.
$76,50 USD em 3 dias
4,8 (54 avaliações)
5,9
5,9
Avatar do Usuário
See private message.
$60,35 USD em 3 dias
5,0 (42 avaliações)
5,5
5,5
Avatar do Usuário
See private message.
$76,50 USD em 3 dias
4,9 (31 avaliações)
4,4
4,4
Avatar do Usuário
See private message.
$50,15 USD em 3 dias
5,0 (5 avaliações)
2,9
2,9
Avatar do Usuário
See private message.
$76,50 USD em 3 dias
5,0 (7 avaliações)
3,0
3,0
Avatar do Usuário
See private message.
$76,50 USD em 3 dias
0,0 (0 avaliações)
0,0
0,0
Avatar do Usuário
See private message.
$70,55 USD em 3 dias
0,0 (0 avaliações)
0,0
0,0

Sobre o cliente

Bandeira do(a) UNITED KINGDOM
Croydon, United Kingdom
5,0
30
Método de pagamento verificado
Membro desde out. 11, 2011

Verificação do Cliente

Obrigado! Te enviamos um link por e-mail para que você possa reivindicar seu crédito gratuito.
Algo deu errado ao enviar seu e-mail. Por favor, tente novamente.
Usuários Registrados Total de Trabalhos Publicados
Freelancer ® is a registered Trademark of Freelancer Technology Pty Limited (ACN 142 189 759)
Copyright © 2024 Freelancer Technology Pty Limited (ACN 142 189 759)
Carregando pré-visualização
Permissão concedida para Geolocalização.
Sua sessão expirou e você foi desconectado. Por favor, faça login novamente.